On 10/22/2016 05:55 AM, Anton Baranenko wrote:
Hi all,
I'd like to ask for your help with implementing the following.
I have quite a complex query with subqueries generated in the
application. In general the structure would look like this:
draft_data = aliased(
Draft.query.filter(
Draft.author_user_id == drafts_from_user_id # draft_from_user_id is a bind
parameter
).subquery(),
name='draft_data'
)
then a bunch of similar subqueries that get integrated into the main
query like this:
query = query.outerjoin(draft_data, WorkItemData.draft_id == draft_data.c.id)
The actual query has around six parametrized subqueries like this.
The resulting query is pretty fast, but it's compilation takes about 50%
to 100% time of its execution => I decided to go with the BakedQuery.
However from the BakedQuery documentation I could not figure out how I
should use it with joins / subqueries. Should I BakedQuery subqueries
separately and then somehow join them with the main BakedQuery (how?).
Should I somehow do the BakedQuery only of the main query (what to do
with subqueries then?).
The thing about BakedQuery is that it is caching the fully formed SQL
string. There is no way to use it only part of the way, when you want
your query to get the performance enhancement of "baked", the final,
outermost query has to be BakedQuery, which means every function that
generates a portion of the query inside of it must produce the identical
SQL in all cases, otherwise you can't cache that end product.
In the case of a subquery, subquery() actually produces a core Select /
Alias object so you're no longer a Query, but it does that by doing the
same compile_context() which is part of the computation time you're
looking to save. So you'd probably want the production of that
subquery to also be inside of a callable function that won't get called
if the BakedQuery is pulling from the cache, although if it's not, it's
not that critical either.
The main thing is always that every function you pass to BakedQuery must
produce a Query object that will result in the identical SQL every time.
So the parameters are the biggest part of it, that is, any kind of
dynamic value needs to be outside of the callables, and bindparam() used
internally.
An example would be:
def do_a_thing(session, thing, otherthing):
query = bakery(lambda s: s.query(SomeClass))
# approach #1: don't "bake" the subquery part. use parameters
# always though
subq = session.query(OtherClass.id).filter(OtherClass.thing ==
bindparam('thing')).subquery()
query += lambda q: q.join(subq, subq.c.id == SomeClass.other_id)
# approach #2: "bake" the subquery too.
def other_subq(q):
subq =
q.session.query(ThirdClass.id).filter(ThirdClass.otherthing ==
bindparam('otherthing')).subquery()
q = q.join(subq, subq.c.id == SomeClass.thirdthing_id)
return q
query += other_subq
return query(session).params(thing=thing, otherthing=otherthing)
It is definitely a little more weird and tedious to do a "builder"
approach with BakedQuery because you have to be mindful of the "every
function must produce the identical SQL each time" requirement. For
more examples you can see in the unit tests some more complex
constructions like in
https://bitbucket.org/zzzeek/sqlalchemy/src/1f32d014da5d40406cd5d3996be5283c2fc57b26/test/ext/test_baked.py?at=master&fileviewer=file-view-default#test_baked.py-466
, and the synopsis at
http://docs.sqlalchemy.org/en/latest/orm/extensions/baked.html#synopsis
shows a little bit how things look as well.
Thank you for any help,
Anton
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
