On May 26, 2010, at 1:39 PM, David Gardner wrote:
> I have a users table, with a lazy=False, innerjoin=True relation to the
> preferences table
> (these could probably be the same table but they are separate for historical
> reasons).
>
> One of the gotchas that I am running into is when I do an outerjoin on to the
> user's table,
> it effectively becomes a join.
>
> r = session.query(Report).options(eagerload(Report.Users)).get(report_name)
>
> returns 0 rows when report_name doesn't have any subscribed users I can and
> do work around this
> by assing in a options(lazyload('Users.Preferences')) to the query.
>
> The generated SQL looks like:
>
> SELECT <bunch of columns>
> FROM report
> LEFT OUTER JOIN user_report AS user_report_1 ON report.name =
> user_report_1.report
> LEFT OUTER JOIN users AS users_1 ON users_1.user_id = user_report_1."user"
> JOIN user_prefs AS user_prefs_1 ON users_1.user_id = user_prefs_1."user"
> WHERE report.name = %(param_1)s
>
> However I wonder if it is feasible for SQLAlchemy to detect this and generate
> this instead:
>
> SELECT <bunch of columns>
> FROM report
> LEFT OUTER JOIN user_report AS user_report_1 ON report.name =
> user_report_1.report
> LEFT OUTER JOIN (users JOIN user_prefs AS user_prefs_1 ON users.user_id =
> user_prefs_1."user" )
> AS users_1 ON users_1.user_id = user_report_1."user"
>
> Like I mentioned, I already have a work-around for this, and I realize this
> is a function of the way I
> defined my mappers.
unfortunately the syntax "x JOIN (y JOIN z)" doesn't work on a lot of backends,
so the eagerloading is designed in such a way as to string all the joins
together from left to right.
im a little curious why I haven't hit this issue myself, a more immediate fix
in SQLA would be to ignore the innerjoin flag when its already down the line of
a string of outer joins.
the "x join (y join z)" allowance could perhaps be worked in as an option at
some point, though this might be elaborate.
In this case, you might find you get better results overall by using
subqueryload(Report.users) instead of eagerload(). You'd get two queries
instead of one, but all the joins would be inner. in the case of no rows
you'd of course only emit one query with no joins at all.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
