Re: [sqlalchemy] Preserve mapped entities after wrapping the query
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_110')), 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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
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.
Re: [sqlalchemy] Preserve mapped entities after wrapping the query
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_110')), 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.
Re: [sqlalchemy] Preserve mapped entities after wrapping the query
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_110')), 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.
[sqlalchemy] Preserve mapped entities after wrapping the query
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 :( -- 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.
Re: [sqlalchemy] Preserve mapped entities after wrapping the query
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+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.
