On Jan 15, 2014, at 12:58 PM, Matt Schmidt <[email protected]> wrote:
> So here's a simplified version of the models I have: > http://pastie.org/private/smqzkvz4zj46skfmipruw > Python==3.3 > SQLAlchemy==0.9.1 > psycopg2==2.5.2 > Postgresql 9.2 > > Running the following query, > session.query(Training).filter(Training.start_date > '2013-11-01') > > Gives me the following SQL: > SELECT trainings.id AS trainings_id, trainings.title AS trainings_title, > view_training_start_dates_1.start AS view_training_start_dates_1_start > FROM view_training_start_dates, trainings LEFT OUTER JOIN > view_training_start_dates AS view_training_start_dates_1 ON trainings.id = > view_training_start_dates_1.training_id > WHERE view_training_start_dates.start > '2013-11-01' > > There are two problems. In the FROM clause, view_training_start_dates should > not be there, and in the WHERE clause, `view_training_start_dates.start` > should be `view_training_start_dates_1.start`. > > Is what I'm trying possible with hybrid properties? Or should I be looking to > alter the query instead? > > Note: I tried doing a simple select expression for start_date, but it wasn't > performing as well as I'd like. the issue here is that when you say “query(Training).filter(Training.start_date > date)", that is shorthand for "query(Training).filter(ViewTrainingStartDates.start > date)”. That is, it’s as though you wrote “SELECT * FROM table1, table2” without using JOIN, hence your statement has “FROM view_training_start_dates, trainings” in it. The “LEFT OUTER JOIN” is an entirely separate thing that is a result of the lazy=“joined” (e.g. the eager load) on Training.training_start_date and does not affect rows matched. The “view_training_start_dates_1” table is part of the eager load and is not accessible to the Query in any other way (see below). So when you say "query(A).filter(B.x > y)”, you need to JOIN, that is, "query(A).join(A.bs).filter(B.x > y)”. Background on this technique specific to the usage of hybrid attributes is at: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/hybrid.html#join-dependent-relationship-hybrid. As far as what the LEFT OUTER JOIN is all about and why you can’t touch any of those columns directly, see: http://docs.sqlalchemy.org/en/rel_0_9/faq.html#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join which will then lead you to http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html as well as http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#the-zen-of-eager-loading.
signature.asc
Description: Message signed with OpenPGP using GPGMail
