On 4/25/15 6:05 AM, Юрий Пайков wrote:
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
OK, more like your original though you can still put the window function
on the inside, and refer to it on the outside:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
field = Column(Integer)
s = Session()
avg = func.avg(A).over(partition_by=A.id).label('avg') # 'avg' label
is optional, will be auto-labeled anyway
q = s.query(A, avg).from_self().filter(avg > 10)
print(q)
in the output, we can see that referring to "avg" the from_self() picks
up on this and adapts it to the inner query:
SELECT anon_1.a_id AS anon_1_a_id, anon_1.a_field AS anon_1_a_field,
anon_1.avg AS anon_1_avg
FROM (SELECT a.id AS a_id, a.field AS a_field, avg(:avg_1) OVER
(PARTITION BY a.id) AS avg
FROM a) AS anon_1
WHERE anon_1.avg > :param_1
суббота, 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
<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
<http://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<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
<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.
