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 [email protected]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
