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?
--
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.
