Emil Ivanov wrote:
>
> As far as I understand eager loading it works by adding a join clause
> to the select. That has the drawback of increased number or rows
> returned. Imagine having 100 objects and each with 200 related
> objects, most of which overlap so the total number of related objects
> is about 300. That will generate 20 000 rows, where in reality it
> would be much better to fetch the 100 objects with a single query and
> then fetch the 300 related objects with another query.
>
> It that possible with the current version of sqlalchemy.
>
> Hibernate has something like this in the form of Batch fetching:
> http://docs.jboss.org/hibernate/stable/core/reference/en/html_single/#performance-fetching-batch
its not built in but you can run the second query manually, using the
first query as a subquery to formulate the WHERE criterion for the second.
I do this often, in fact.
there is a helper method sqlalchemy.orm.attributes.set_committed_value()
which you can use to populate the related scalar or collections of each
parent object, without any history events firing off, so that the related
object(s) will have the state of objects that were loaded from the
database.
the general idea is:
q= sess.query(Parent).filter(...)
parents = q.all()
children =
sess.query(Child).filter(Child.id.in_(sess.query(Parent.id).filter(...))
parent_dict = dict((p.id, []) for p in parents)
for child in children:
parent_dict[child.parent_id].append(child)
for p in parents:
set_committed_value(p, "children", parent_dict[p.id])
there are several reasons this is not built in. one is that the subquery
step is very difficult to formulate for relations that deal with complex
join conditions and composite primary keys - by leaving this to the user
SQLA avoids wading into what is probably kind of a bottomless hole. The
other is that the ORM doesn't include an "immediate second query" in its
architecture, nor does it include a "deferred load among many objects from
a previous query in response to a single attribute access" - which is
essentially the "lazy" version of this and is often what people prefer.
there are two features though which would make the above operation simpler
- a query.select_entities(*cols_or_entities) method, which is essentially
what query.values() does without the execution (I always preferred it that
way, in fact), and perhaps a "knit_collections_together" type of method
that would do the second step.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
