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