RE: [sqlalchemy] Possible bug with subqueryload
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 27 September 2011 19:37 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Possible bug with subqueryload On Sep 27, 2011, at 1:16 PM, King Simon-NFHD78 wrote: Great, thanks a lot :-) I only discovered it in a toy application, and the workaround (including order_by on the query) is not a problem. In this toy application, I was also wondering if there existed a mechanism for doing some sort of lazy subqueryload. ie. I'm loading a collection of objects and I don't know ahead of time if I'm going to access a particular relationship (so I don't want to eagerload it). However, if I *do* access it, I'm going to access it on each object in the collection, so I'd like to load all the related objects in a single hit. It's just like a subqueryload, except it is only executed when the relationship is accessed for the first time. Is that a silly idea? Or perhaps it already exists and I've missed it. It actually exists in Hibernate, but not for us.We do sort of have the infrastructure in place to make it possible, i.e.the subqueryload right now prepares a Query object at query time that fires off during load time, with a mapper option it would need to stick it as some kind of memo in each InstanceState, it would be very tricky to implement.Keeping that state and keeping it plugged into the InstanceStates, then what if the loader was fired after many of the other states have been garbage collected, just a lot of corner cases to deal with. it can be handrolled of course, the general technique when you want to construct objects such that they appear loaded is to use attributes.set_commited_value() to set an attribute such that the ORM sees it as what was loaded from the database. an example of that is where we first introduced the subquery concept here: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading Ah yes, set_committed_value is exactly the sort of thing I was looking for. Thanks a lot, Simon -- 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.
Re: [sqlalchemy] Possible bug with subqueryload
that mapper.order_by thing is fixed in 0.7.3/0.6.9 tip. -- 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.
Re: [sqlalchemy] Possible bug with subqueryload
On Wed, Sep 28, 2011 at 2:15 PM, Michael Bayer mike...@zzzcomputing.comwrote: that mapper.order_by thing is fixed in 0.7.3/0.6.9 tip. Brilliant - thanks again for all the time you put in to SA and this group, Simon -- 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.
Re: [sqlalchemy] Possible bug with subqueryload
Hi Simon -
yeah that looks pretty buglike to me, mapper.order_by is not a frequently used
feature so this one may need some adjustment.
I've created http://www.sqlalchemy.org/trac/ticket/2287 to take a look at this
and so far I'm targeting it at 0.6.9/0.7.3.
On Sep 27, 2011, at 7:29 AM, King Simon-NFHD78 wrote:
Hi,
I think there may be a bug in the interaction between 'subqueryload' and
having a default 'order_by' defined on a mapped class. When the subquery
is run, it looks like the ORDER BY is being placed on the outer query,
whereas it should be on the inner query. The full test case is below,
but here are the 2 queries (produced using hg revision 62e97372a028):
Main query
--
SELECT master.id AS master_id, master.dummy AS master_dummy
FROM master
ORDER BY master.id DESC
LIMIT 2 OFFSET 0
Subquery
SELECT detail.id AS detail_id, detail.master_id AS detail_master_id,
anon_1.master_id AS anon_1_master_id
FROM (SELECT master.id AS master_id
FROM master
LIMIT 2 OFFSET 0) AS anon_1
JOIN detail ON anon_1.master_id = detail.master_id
ORDER BY anon_1.master_id
Since the ORDER BY is not on the inner query, a different set of
'master' rows is referenced than in the main query.
Cheers,
Simon
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Master(Base):
__tablename__ = 'master'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
dummy = sa.Column(sa.Integer)
__mapper_args__ = {'order_by': sa.desc(id)}
class Detail(Base):
__tablename__ = 'detail'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
master_id = sa.Column(sa.Integer, sa.ForeignKey(Master.id))
master = saorm.relationship(Master, backref='details')
def test():
dburi = 'sqlite://'
engine = sa.create_engine(dburi, echo=True)
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)
session = saorm.create_session(bind=engine)
session.begin()
# Insert 5 masters, each with 1 detail
for i in range(5):
master = Master(dummy=i)
master.details.append(Detail())
session.add(master)
session.commit()
session.close()
# Load back 2 masters, using subqueryload to load the detail
# rows. If you uncomment the '.order_by' line here, the test
# passes.
master_query = (session.query(Master)
#.order_by(sa.desc(Master.id))
.limit(2)
.options(saorm.subqueryload('details')))
# Display the details for each master
for item in master_query:
print 'Master %s: %s' % (item.id, item.details),
if len(item.details) == 0:
print 'FAIL'
else:
print 'PASS'
if __name__ == '__main__':
test()
--
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.
--
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.
RE: [sqlalchemy] Possible bug with subqueryload
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 27 September 2011 16:24 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Possible bug with subqueryload Hi Simon - yeah that looks pretty buglike to me, mapper.order_by is not a frequently used feature so this one may need some adjustment. I've created http://www.sqlalchemy.org/trac/ticket/2287 to take a look at this and so far I'm targeting it at 0.6.9/0.7.3. Great, thanks a lot :-) I only discovered it in a toy application, and the workaround (including order_by on the query) is not a problem. In this toy application, I was also wondering if there existed a mechanism for doing some sort of lazy subqueryload. ie. I'm loading a collection of objects and I don't know ahead of time if I'm going to access a particular relationship (so I don't want to eagerload it). However, if I *do* access it, I'm going to access it on each object in the collection, so I'd like to load all the related objects in a single hit. It's just like a subqueryload, except it is only executed when the relationship is accessed for the first time. Is that a silly idea? Or perhaps it already exists and I've missed it. Thanks, Simon -- 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.
Re: [sqlalchemy] Possible bug with subqueryload
On Sep 27, 2011, at 1:16 PM, King Simon-NFHD78 wrote: Great, thanks a lot :-) I only discovered it in a toy application, and the workaround (including order_by on the query) is not a problem. In this toy application, I was also wondering if there existed a mechanism for doing some sort of lazy subqueryload. ie. I'm loading a collection of objects and I don't know ahead of time if I'm going to access a particular relationship (so I don't want to eagerload it). However, if I *do* access it, I'm going to access it on each object in the collection, so I'd like to load all the related objects in a single hit. It's just like a subqueryload, except it is only executed when the relationship is accessed for the first time. Is that a silly idea? Or perhaps it already exists and I've missed it. It actually exists in Hibernate, but not for us.We do sort of have the infrastructure in place to make it possible, i.e.the subqueryload right now prepares a Query object at query time that fires off during load time, with a mapper option it would need to stick it as some kind of memo in each InstanceState, it would be very tricky to implement.Keeping that state and keeping it plugged into the InstanceStates, then what if the loader was fired after many of the other states have been garbage collected, just a lot of corner cases to deal with. it can be handrolled of course, the general technique when you want to construct objects such that they appear loaded is to use attributes.set_commited_value() to set an attribute such that the ORM sees it as what was loaded from the database. an example of that is where we first introduced the subquery concept here: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading Thanks, Simon -- 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. -- 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.
