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.
[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 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 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 subqueryloa
[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 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
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 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.
