Hello all,
I am trying to optimize a query I have, and have noticed some strange
behavior. I have a table called 'Trade'. A trade may optionally have a
commission, which itself is a trade. This is the core of it:
class Trade(Base, CCPTableMixin):
__tablename__ = "trade"
id = Column(Integer, primary_key=True)
trade_commission = Table("trade_commission",
Base.metadata,
Column("id_trade_victim", Integer, ForeignKey(id),
primary_key=True),
Column("id_trade_cash", Integer, ForeignKey(id),
primary_key=True))
commission = relationship(trade_class_name,
secondary=trade_commission,
primaryjoin=(id ==
trade_commission.c.id_trade_victim),
secondaryjoin=(id ==
trade_commission.c.id_trade_cash),
uselist=False,
backref=backref("commission_for",
uselist=False))
Now, in a particular query, if a trade happens to be a commission trade, I
wish to eagerly load the original 'victim' trade, so I have something like
this:
session().query(Trade)
query = query.options(joinedload("commission_for"))
I noticed things were going a bit slowly, and to my surprise when I turned
on query tracing I could see many individual queries. It turns out that the
joined loading was working in general, however, if 'commission_for' was
None (e.g. the trade is not a commission trade) SQLAlchemy was issuing a
query to re-populate the (empty) collection.
Interestingly, when I switched it to 'subqueryload' - it didn't do this and
correctly eagerly loaded the empty collection. However, I have quite an
expensive query to select the list of trades in the first place which I
would rather not do twice, which is required for a subquery eager load. (It
would be really cool if you could select the primary keys into a temporary
table or something when doing a subquery load, but I guess that would be
quite difficult to do in a DB independant way)
Is this a bug, or perhaps some expected side effect of the joined load?
I am using SQLAlchemy 0.9.3. If it help I could knock up a working example
in short order.
Cheers, and keep up the fine work!
- Philip
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
