Ok, I seemed to figure out how to deal with it -
row_number_column = func.row_number().over(
partition_by=Recipe.id
).label('row_number')
query = query.add_column(
row_number_column
)
query = query.from_self().filter(row_number_column == 1)
Using an explicit column construct
суббота, 25 апреля 2015 г., 11:39:08 UTC+5 пользователь Юрий Пайков написал:
>
> Michael, thank you for you reply, I expected you to mention from_self :) I
> know about it, it is a handy trick indeed
> But I deliberately don't use it, because this way I don't know how to
> mention a column which I want to filter on
> This is due to the fact, that it is calculated i.e. there is no table to
> refer to! I might resert to using literals("filter('avg_1>10')"), but 'd
> prefer to stay in the more ORM-style
>
>
> суббота, 25 апреля 2015 г., 2:37:11 UTC+5 пользователь Michael Bayer
> написал:
>>
>>
>>
>> On 4/24/15 5:25 PM, Пайков Юрий wrote:
>>
>>
>> q = session.query(Recipe, func.avg(Recipe.field1).over(...)).join(...)
>>
>> I have a query which selects some mapped entity as well as other columns.
>>
>> I then refer to the name of that entity when working with the result of
>> the query:
>> for entry in q.all():
>> recipe=entry.Recipe
>> ....
>>
>> Now, I want to add filtering by some calculated criteria to my query, and
>> so I wrap it in an additional query:
>> q = q.subquery();
>> q = session.query(q).filter(q.c.avg_1 > 10 )
>>
>> However, this way I can no longer access entry.Recipe! Is there a way to
>> make sqlalchemy adapt names? I tried aliased and select_entity_from, but no
>> luck :(
>>
>>
>> this is getting into less reliable stuff, but instead of subquery() ->
>> session.query(q), use the from_self() method. It's designed to work this
>> way, and your Recipe entity will be adapted into the subquery.
>>
>> I've observed that the vast majority of my users don't seem to get into
>> queries like these, so from_self() is not as popular (or widely tested) as
>> it should be, but it is at the base of a lot of widely used functions like
>> count() and subquery eager loading, so give it a try:
>>
>>
>> http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=from_self#sqlalchemy.orm.query.Query.from_self
>>
>> apparently it needs some documentation too :)
>>
>>
>> --
>> 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+...@googlegroups.com.
>> To post to this group, send email to sqlal...@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.
