On Jun 24, 2010, at 10:04 PM, Nicholas Bower wrote:
> I seem to have found a condition in which eager loading decouples an
> applied filter, distorting results an an unexpected way.
>
> I have the following simple 1:N:1 table structure
>
> t_swath_metadata: swath_id, ...
> t_productfile: swath_id, product_id, filename, ...
> t_product: product_id, product_name
>
> which is just a logical entity, related files and their file types.
> This is mapped to the following using SQL Alchemy 0.5.x
>
> class SwathMetadata(object):
> pass
> class ProductFile(object)
> pass
> class Product(object)
> pass
>
> t_swath_metadata = Table('t_swath_metadata', metadata,
> Column('swath_id', Integer, primary_key=True), ...)
>
> t_productfile = Table('t_productfile', metadata,
> Column('filename', String, primary_key=True),
> Column('product_id', Integer,
> ForeignKey('wastac.t_product.product_id')),
> Column('swath_id', Integer,
> ForeignKey('wastac.t_swath_metadata.swath_id')), ...)
>
> t_product = Table('t_product', metadata,
> Column('product_id', Integer, primary_key=True),
> Column('product_name', String) ...)
>
> swathMapper = mapper(SwathMetadata, t_swath_metadata,
> properties={'productfile': relation(ProductFile)})
>
> productFileMapper = mapper(ProductFile, t_productfile,
> properties={'product': relation(Product)})
>
> productMapper = mapper(Product, t_product)
>
>
> Using this I want to query t_swath_metadata based on that joined
> t_product using the following
>
> q = sess.query(SwathMetadata)
> q = q.join((ProductFile, ProductFile.swath_id ==SwathMetadata.swath_id))
> q = q.join((Product, Product.product_id ==ProductFile.product_id))
> q = q.filter(Product.product_name=='qlgt')
> results = q.all()
>
> No problem. But I also want access to t_productfile in the results.
> So expectedly using the instrumented attribute like this
>
> results[i].productfile.filename
>
> results in additional undesired loads for each result[i]. Enabling
> eager loading seemed logical, so using
>
> q = q.options(eagerload(SwathMetadata.productfile))
>
> is where the problem comes in. Each SwathMetadata result is now no
> longer attached to single SwathMetadata.productfile (as implied by the
> Product.product_name=='qlgt' filter), but attached to many ProductFile
> instances, thus completely ignoring the filter.
>
> Behind the scenes I think the eager load is disrupting things causing
> an enforced outer join and subquery:
>
> SELECT foo.*, t_productfile.* from (SELECT t_swath_metadata.* FROM
> t_swath_metadata JOIN t_productfile JOIN t_product WHERE
> t_product.product_name = 'qlgt') as foo LEFT OUTER JOIN t_productfile;
>
> And this is wrong given my applied filter because although the right
> SwathMetadata results are returned, traversing
> SwathMetadata.productfile now returns all instances of ProductFile,
> not just the ones related to Product.product_name = 'qlgt'.
>
> How do I get around this?
eagerload() doesn't do anything with the existing joins or filter criterion you
have, it specifically generates its own joins that will load everything that is
logically part of the SwathMetadata.productfile relationship, which here is a
one-to-many. The purpose here is to separate the concern of populating a
collection from that of the filtering/joining intended to locate the primary
object rows. In the typical Parent->Child one-to-many scenario, you might
want to load Parent id 2, because its the one that's linked to Child id 12, but
once you have that Parent, you'd like its "children" collection to represent
the full list of Child objects referenced by the Parent, not just Child 12.
Here, you'd like the joins and such that you've spelled out manually to also
result in the population of SwathMetadata.productfile, limiting the collection
to only those items selected by your joins. For this purpose, use the
contains_eager() option, introduced in the ORM tutorial at:
http://www.sqlalchemy.org/docs/ormtutorial.html?#using-join-to-eagerly-load-collections-attributes
(note joinedload() is the same as eagerload() in 0.5)
and described in more detail at:
http://www.sqlalchemy.org/docs/mappers.html#routing-explicit-joins-statements-into-eagerly-loaded-collections
Also, if the SwathMetadata.productfile relationship is really intended to point
to one specific ProductFile, you also might consider specifying the
"primaryjoin" of the relationship so that when queried it loads what is
intended to be a member of the collection.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
