On May 25, 2011, at 12:34 PM, mdob wrote:
> Consider a simple model like this:
>
> Library 1---* Shelf 1---* Book
>
> Accordingly our model has the following relationships declared:
>
> {tu wkelj kod z relationship wlaczajac backrefy)
>
> We will write a query with a join to get all three objects at once
>
> SELECT *
> FROM Library
> JOIN Shelf ON Library.id = Shelf.library_id
> JOIN Shelf ON Shelf.id = Book.shelf_id
> WHERE Library.name = 'TheLibrary' AND Shelf.loc = 'A1' AND Book.title
> = 'Ender''s Game'
>
> q = session.query(Library)
> q = q.join(Shelf)
> q = q.join(Book)
> q = q.filter(Library.name = "TheLibrary")
> q = q.filter(Shelf.loc = "A1")
> q = q.filter(Book.title = "Ender's Game")
> q = q.options(contains_eager('shelves', 'books'))
> result = q.all()
>
> Let's assume is only one record that meets the criteria (we have only
> one book with this title on shelf A1). We expect to get a single
> Library object with one-element collection of shelves, which has one
> element colection of books. This is indeed what seems to come back.
>
> However, upon access to a backref'd library _attribute_ in Shelf
> object another database query again Library gets automatically
> executed. This seems unneccesary because the related Library object is
> already loaded into the session.
>
> I've figured out that it has something to do with backref to from
> Shelf to Library so I added a line:
>
> q = q.options(contains_eager('shelves', 'Library'))
>
> and now the supurios query does not run.
>
> 1. Are these backref paths required? It can get messy with deeper
> relationships.
Backrefs usually take care of themselves as they are typically many-to-ones
that load directly from the identity map with no SQL query needed. If they are
not many to ones, and instead represent a collection, then it clearly has to go
to the DB to get the collection, even though one element of that collection was
loaded on the m2o side. So if you're doing one-to-one, you'd prefer to
contains_eager() the parent->FK side. I think that's what you're doing here.
In this case it appears you're saying Shelf.library is many-to-one but is still
invoking a load. This would happen in one of two cases: 1. the Library has
already been garbage collected from the Session by the time you call
Shelf.library, or 2. the many-to-one has a non-trivial join condition so that
the "get" logic cannot take place. The contains_eager() you're doing will
ensure Shelf.library is populated, as would "immediateload(Shelf.library)".
> 2. Indidentally accessing shelf attribute on Book DOES NOT result in a
> query? Why this inconsitence or am I missing something?
depends on #1 and #2 in my previous paragraph.
> 3. How will create_eager behave with many-to-many relationship?
m2m will work pretty much like the o2m case, you'd of course need to join to
your association table, which you can do via q.join(Library.shelf),
q.join(Shelf.book). I would not recommend using m2m for a scalar reference
however.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
