On 6/18/14, 2:28 PM, Mike Solomon wrote: > Hey all, > > First and foremost, thank you for this wonderful library! This is my > first post to the list and I am very grateful for those who have taken > the time to make sqlalchemy. > > I am using SQLalchemy as a backend for an object-oriented language I > am developing. The way it works is that it is developed in python and > SQLalchemy outputs SQL that represents the entire logic of the program. > > The ORM has been a great way to do this, but there is one hurdle that > I continue to face in my development strategy: the creation of > subqueries that behave like classes. > > For example, if I have a class representing a table: > > class Holder(Base) : > id = Column('id', Integer, primary_key=True) > house = Column('house', Integer) > team = Column('team', Integer) > duration_num = Column('duration_num', Integer) > duration_den = Column('duration_num', Integer) > duration = column_property(1 * duration_num / duration_den) > > what would be nice is to do something like: > > query(Holder.house, Holder.team, > MAX(Holder.duration)).group_by(Holder.house, Holder.team)
you can, just for now there's a workaround say func.max(Holder.duration.expression), ticket #3066 should fix this > > where MAX was intelligent enough to expand the query into two queries > on Holder joined to each other: one that found the max of duration and > another that did a join to the original table to get the corresponding > num and den. when you say "two queries", if you mean like a nested SELECT statement, your column_property() would have to refer to that. Based on your example the statement would be: SELECT house, team, MAX(1* duration_num / duration_den) FROM holder GROUP BY house, team > I discuss this in the stack overflow > post: > http://stackoverflow.com/questions/24257066/sql-legality-of-including-ungrouped-columns-in-group-by-statement. > > I've currently been doing this type of joining by hand, but that weds > my abstraction of the column property to its internals. What would be > great is to be able to define aggregate functions on column properties > and what behavior they'd result in. Perhaps this is easy and I've > missed how to do it, but so far I haven't found any good solutions. > Any help would be appreciated! if you can show the SQL you expect that would help. it seems in your SO question you want a subquery, you'd have to define that: class Holder(..): some_prop = column_property(select([func.max(1 * col1 / col2)])) > > Cheers, > Mike > -- > You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send > an email to sqlalchemy+unsubscr...@googlegroups.com > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.