Thanks. That's quite an interesting piece of code. There's a bit of magic
happening in this code and it's not quite compatible for my use case (use
of queries instead of tables, no ORM mapping), so allow me to ask some
questions. I've annotated the code, so perhaps you can correct any of my
assumptions that are wrong. My aim is to apply a similar concept to two
queries that are not mapped to a class.
def disjoint_load(query, attr):
# This is just to extract the join condition.
target = attr.prop.mapper
local_cols, remote_cols = zip(*attr.prop.local_remote_pairs)
# As far as I can tell, this creates a SELECT from the original parent
query.
# I'm not sure how this join works, as `attr` is a reference to
# `Parent.children` (no a condition), but I guess I could replace it
# with a condition that I pass in to the function.
# The `order_by` may not be necessary...
# Question: Does this also work if `target` already is a select query
containing a CTE?
child_q = query.from_self(target).join(attr).order_by(*remote_cols)
if attr.prop.order_by:
# No idea why/what this does. Is this necessary?
child_q = child_q.order_by(*attr.prop.order_by)
# This is creating an identity map (parent id -> children list), but how do
we
# know the `parent.id` at this point? The query hasn't been issued yet...
collections = dict((k, list(v)) for k, v in groupby(
child_q,
lambda x:tuple([getattr(x, c.key) for c in remote_cols])
))
# `engine.echo=True` revealed that this is issuing 2 queries (which is what
I want)
# The order is (1) query for children (joining on parent query), (2) parent
query
# How/where is the children query attached to the parent query and where is
it sent?
parents = query.all()
# This does the final assignment of 'list of children per parent' ->
parent.children
for p in parents:
attributes.set_committed_value(
p,
attr.key,
collections.get(
tuple([getattr(p, c.key) for c in local_cols]),
())
)
return parents
This is pretty much what I was looking for, but it needs a bit of tinkering
to work for me. Do you think it's advisable to use some dummy classes to
map the two queries to in order to reuse as much as possible from the above
(or adapt it to work with select queries)? What would be the implications
in terms of performance (would any of the ORM features such as attribute
tracking, identity map, etc. that I don't necessarily need be used in such
a case)?
On Tuesday, September 3, 2013 1:39:44 AM UTC+10, Michael Bayer wrote:
>
>
> On Aug 31, 2013, at 7:33 PM, gbr <[email protected] <javascript:>>
> wrote:
>
> On Sunday, September 1, 2013 8:19:24 AM UTC+10, Michael Bayer wrote:
>
>>
>> Let me add a bit of information. I assumed the behaviour would be the
> same in ORM (actually, when looking at the docs again I saw it's actually
> called "subquery eager loading", i.e. issuing an additional query upon
> receiving a bunch of table rows which other tables relate to).
> The first query I sent is a join of other tables, has a where clause and a
> CTE. The 2nd query also contains a CTE (the same as the 1st). I know the
> columns to join on and can specify them. A result proxy is fine as a result.
>
> Oh, is it that easy? Is this the same for a subquery eager load? I tried
> to use this, but ran into an issue (`CompileError: Multiple, unrelated CTEs
> found with the same name`). I could use just one CTE, as they are the same
> for both queries, but I don't know how to remove it from the subqueried
> (query 1) query.
>
>
> OK a subquery eager load we can do, because that started out as a recipe
> and you can see that here:
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading
>
>
>
--
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/groups/opt_out.
