[sqlalchemy] order_by with explicit column name messes up subqueryload
Hi, I have a rather complicated problem and I was wondering if you guys could help. So I have a query, session.query(Product, Merchant, d), where Product is 1-to-many with Merchant, and d is the distance from some lat long. d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy function calls. Product has a number of collections in which I would like to load using subqueryload_all() as well, and the result is ordered by distance as in order_by(distance), where distance is the name of the label d. My problem is, since I'm supplying the Query object with an explicit order_by() name, when I use subqueryload(), the order_by() name is put into the subquery as is, because SQLAlchemy doesn't know any better with a plain string. If I pass in a column element, SQLAlchemy seems to know not to put an ORDER BY in the subquery. This seems to me like a bug because a subqueryload() always join on the primary key of the previous SELECT, so unless the name is the primary key name, it really shouldn't be put in the subquery. So finally my question, if this is too hard to fix, is there an option somewhere that I can tell SA to ignore the previous order_by() when doing a subqueryload()? If not, and I can't wait for a fix now, is there a way where I can turn my distance label into a ClauseElement so that SA knows not to put into the subquery when loading collections? Thanks in advance! -- 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.
[sqlalchemy] Re: order_by with explicit column name messes up subqueryload
BTW, aliased() and alias() don't work on a label() either. I tried passing the label object straight into the order_by() as well to no avail. I'm all out of ideas. On Jan 9, 3:47 am, Yuen Ho Wong wyue...@gmail.com wrote: Hi, I have a rather complicated problem and I was wondering if you guys could help. So I have a query, session.query(Product, Merchant, d), where Product is 1-to-many with Merchant, and d is the distance from some lat long. d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy function calls. Product has a number of collections in which I would like to load using subqueryload_all() as well, and the result is ordered by distance as in order_by(distance), where distance is the name of the label d. My problem is, since I'm supplying the Query object with an explicit order_by() name, when I use subqueryload(), the order_by() name is put into the subquery as is, because SQLAlchemy doesn't know any better with a plain string. If I pass in a column element, SQLAlchemy seems to know not to put an ORDER BY in the subquery. This seems to me like a bug because a subqueryload() always join on the primary key of the previous SELECT, so unless the name is the primary key name, it really shouldn't be put in the subquery. So finally my question, if this is too hard to fix, is there an option somewhere that I can tell SA to ignore the previous order_by() when doing a subqueryload()? If not, and I can't wait for a fix now, is there a way where I can turn my distance label into a ClauseElement so that SA knows not to put into the subquery when loading collections? Thanks in advance! -- 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] order_by with explicit column name messes up subqueryload
On Jan 8, 2012, at 2:47 PM, Yuen Ho Wong wrote: Hi, I have a rather complicated problem and I was wondering if you guys could help. So I have a query, session.query(Product, Merchant, d), where Product is 1-to-many with Merchant, and d is the distance from some lat long. d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy function calls. Product has a number of collections in which I would like to load using subqueryload_all() as well, and the result is ordered by distance as in order_by(distance), where distance is the name of the label d. My problem is, since I'm supplying the Query object with an explicit order_by() name, when I use subqueryload(), the order_by() name is put into the subquery as is, because SQLAlchemy doesn't know any better with a plain string. If I pass in a column element, SQLAlchemy seems to know not to put an ORDER BY in the subquery. This seems to me like a bug because a subqueryload() always join on the primary key of the previous SELECT, so unless the name is the primary key name, it really shouldn't be put in the subquery. So finally my question, if this is too hard to fix, is there an option somewhere that I can tell SA to ignore the previous order_by() when doing a subqueryload()? If not, and I can't wait for a fix now, is there a way where I can turn my distance label into a ClauseElement so that SA knows not to put into the subquery when loading collections? any chance you can save me some time and attach a complete, succinct .py example here ? subqueryload removes the ORDER BY from the query, provided LIMIT/OFFSET aren't present, unconditionally. It doesn't care that it's a string or not. http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/orm/strategies.py#L754 -- 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.
[sqlalchemy] Re: order_by with explicit column name messes up subqueryload
Except that LIMIT and OFFSET are present in my query, gnarly isn't it ? :P d = label(distance, some_complicated_geoalchemy_function_call(columns...)) q = session.query(Product, Merchant.location, d)\ .join(Merchant, Product.merchant_id == Merchant.id)\ .filter(Product.numinstock 0)\ .options(subqueryload_all(Product.origin, Product.style, Product.foods, Product.flavors, Product.occasions, Product.moods, Product.varieties)) q = q.order_by(distance).offset(0).limit(20).all() On Jan 9, 3:57 am, Michael Bayer mike...@zzzcomputing.com wrote: On Jan 8, 2012, at 2:47 PM, Yuen Ho Wong wrote: Hi, I have a rather complicated problem and I was wondering if you guys could help. So I have a query, session.query(Product, Merchant, d), where Product is 1-to-many with Merchant, and d is the distance from some lat long. d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy function calls. Product has a number of collections in which I would like to load using subqueryload_all() as well, and the result is ordered by distance as in order_by(distance), where distance is the name of the label d. My problem is, since I'm supplying the Query object with an explicit order_by() name, when I use subqueryload(), the order_by() name is put into the subquery as is, because SQLAlchemy doesn't know any better with a plain string. If I pass in a column element, SQLAlchemy seems to know not to put an ORDER BY in the subquery. This seems to me like a bug because a subqueryload() always join on the primary key of the previous SELECT, so unless the name is the primary key name, it really shouldn't be put in the subquery. So finally my question, if this is too hard to fix, is there an option somewhere that I can tell SA to ignore the previous order_by() when doing a subqueryload()? If not, and I can't wait for a fix now, is there a way where I can turn my distance label into a ClauseElement so that SA knows not to put into the subquery when loading collections? any chance you can save me some time and attach a complete, succinct .py example here ? subqueryload removes the ORDER BY from the query, provided LIMIT/OFFSET aren't present, unconditionally. It doesn't care that it's a string or not. http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/orm/strategies -- 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.
[sqlalchemy] Re: order_by with explicit column name messes up subqueryload
Here are the models:
class Merchant(Base):
__tablename__ = merchant
id = Column(Integer, autoincrement=True, primary_key=True)
location = GeometryColumn(Point, nullable=False)
class SearchOption(Base):
__tablename__ = searchoption
id = Column(Integer, autoincrement=True, primary_key=True)
parent_id = Column(Integer, ForeignKey(id,
onupdate=CASCADE,
ondelete=CASCADE))
parent = relationship(SearchOption, uselist=False,
remote_side=[id],
backref=backref(children))
discriminator = Column(type, Enum(style, origin, price,
food,
flavor, occasion,
variety, mood,
name=searchoptiontype))
__mapper_args__ = {polymorphic_on: discriminator}
displayname = Column(Unicode(64), nullable=False)
class StyleOption(SearchOption):
__mapper_args__ = {polymorphic_identity: style}
class OriginOption(SearchOption):
__mapper_args__ = {polymorphic_identity: origin}
class FoodOption(SearchOption):
__mapper_args__ = {polymorphic_identity: food}
class FlavorOption(SearchOption):
__mapper_args__ = {polymorphic_identity: flavor}
class OccasionOption(SearchOption):
__mapper_args__ = {polymorphic_identity: occasion}
class VarietyOption(SearchOption):
__mapper_args__ = {polymorphic_identity: variety}
class MoodOption(SearchOption):
__mapper_args__ = {polymorphic_identity: mood}
product_searchoption_table = Table(product_searchoption, metadata,
Column(product_id,
Integer,
ForeignKey(product.id,
onupdate=CASCADE,
ondelete=CASCADE),
primary_key=True),
Column(searchoption_id,
Integer,
ForeignKey(searchoption.id,
onupdate=CASCADE,
ondelete=CASCADE),
primary_key=True))
class Product(Base): # tune full-text search
__tablename__ = product
id = Column(Integer, autoincrement=True, primary_key=True)
origin = relationship(OriginOption, uselist=False,
secondary=product_searchoption_table)
style = relationship(StyleOption, uselist=False,
secondary=product_searchoption_table)
pricerange = relationship(PriceOption, uselist=False,
secondary=product_searchoption_table)
foods = relationship(FoodOption,
secondary=product_searchoption_table)
flavors = relationship(FlavorOption,
secondary=product_searchoption_table)
occasions = relationship(OccasionOption,
secondary=product_searchoption_table)
moods = relationship(MoodOption,
secondary=product_searchoption_table)
varieties = relationship(VarietyOption,
secondary=product_searchoption_table)
merchant_id = Column(Integer, ForeignKey(merchant.id,
onupdate=CASCADE,
ondelete=CASCADE),
nullable=False,)
merchant = relationship(Merchant, uselist=False,
backref=backref(products))
Hope this helps.
On Jan 9, 4:16 am, Yuen Ho Wong wyue...@gmail.com wrote:
Except that LIMIT and OFFSET are present in my query, gnarly isn't
it ? :P
d = label(distance,
some_complicated_geoalchemy_function_call(columns...))
q = session.query(Product, Merchant.location, d)\
.join(Merchant, Product.merchant_id == Merchant.id)\
.filter(Product.numinstock 0)\
.options(subqueryload_all(Product.origin, Product.style,
Product.foods, Product.flavors, Product.occasions, Product.moods,
Product.varieties))
q = q.order_by(distance).offset(0).limit(20).all()
On Jan 9, 3:57 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Jan 8, 2012, at 2:47 PM, Yuen Ho Wong wrote:
Hi,
I have a rather complicated problem and I was wondering if you guys
could help.
So I have a query, session.query(Product, Merchant, d), where Product
is 1-to-many with Merchant, and d is the distance from some lat long.
d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy
function calls.
Product has a number of collections in which I would like to load
using subqueryload_all() as well, and the result is ordered by
distance as in order_by(distance), where distance is the name of
the label d.
My problem is, since I'm supplying the Query object with an explicit
order_by() name, when I use subqueryload(), the order_by() name is put
into the subquery as is, because SQLAlchemy doesn't know any better
with a plain string.
Re: [sqlalchemy] Re: order_by with explicit column name messes up subqueryload
On Jan 8, 2012, at 3:16 PM, Yuen Ho Wong wrote: Except that LIMIT and OFFSET are present in my query, gnarly isn't it ? :P d = label(distance, some_complicated_geoalchemy_function_call(columns...)) q = session.query(Product, Merchant.location, d)\ .join(Merchant, Product.merchant_id == Merchant.id)\ .filter(Product.numinstock 0)\ .options(subqueryload_all(Product.origin, Product.style, Product.foods, Product.flavors, Product.occasions, Product.moods, Product.varieties)) q = q.order_by(distance).offset(0).limit(20).all() LIMIT and OFFSET require an ORDER BY. Ordering is non-deterministic without ORDER BY, so with LIMIT/OFFSET it means your results are non-deterministic as well. In fact you can't even use LIMIT/OFFSET on some databases without ORDER BY present. Without being able to run this it would appear you just need to order_by(d) here. But the distance label should also be rendered into the subqueryload, should just work that way too.Or try order_by(func.some_complex_geoalchemy_function(...)). You can also roll the subqueryload manually (a recipe is at: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading ). If you have further problems please produce a .py file that actually runs - what we need here is just print q to print the SQL generated by the query, doesn't need to actually generate tables. You can omit all the extraneous model objects and column too. -- 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.
