I wasn't having any luck doing the equivalent of inner join ... on true earlier, but apparently I was just doing it wrong. That works. (In fact, I can just use "literal(True)" instead of "literal(1) == 1")
It still feels a little hackish, but at least it works. Thanks! -- Daniel On Friday, April 5, 2013 10:07:55 AM UTC-7, Michael Bayer wrote: > > according to wikipedia, a "CROSS JOIN" is just a straight up cartesian > product. So why not do "a JOIN b on 1 == 1" ? > > query(model_a).join(model_b, literal(1) == 1).outerjoin(model_c, ...) > > > > > On Apr 5, 2013, at 12:52 PM, Daniel Grace <thisgen...@gmail.com<javascript:>> > wrote: > > I've been banging my head against the wall for hours trying to figure out > how to do this in sqlalchemy. After discussing on #sqlalchemy, it was > suggested I ask here so here goes. > > I'm trying to get SQL roughly equivalent to the below, but using the ORM: > > SELECT .... FROM a CROSS JOIN b LEFT JOIN c ON c.a_id=a.id AND c.b_id=b.id > > > > What I can't do is something like this: > > # session.query(model_a, model_b).outerjoin(model_c, model_c.a_id== > model_a.id & model_c.b_id==model_b.id) > > because the resultant SQL becomes this: > > SELECT .... FROM a, b LEFT JOIN c ON c.a_id=a.id AND c.b_id=b.id > > which fails (in Postgres and, IIRC, newer MySQL versions) due to explicit > joins' binding tighter than the comma operator: > > ERROR: invalid reference to FROM-clause entry for table "a"SQL state: > 42P01Hint: There is an entry for table "a", but it cannot be referenced from > this part of the query.Character: 342 > > > > A few notes: > > > I'm using the ORM, but I can't actually use the Many to Many/Association > Object patterns here because 'c' actually depends on three tables, not two. > (In this particular case, I'm only interested in cases of c related to one > particular entry in the third table, so there's no need to query on that > particular relationship). > > > I can't merely rewrite the query to "c INNER JOIN a... INNER JOIN b" because > I'm also interested in the combinations of (a,b) for which there are no c. > (I could technically union it against another query that uses NOT EXISTS, but > this smells of bad hackery). > > > If I could get parenthesis around the a, b, the query would work as intended: > SELECT .... FROM (a, b) LEFT JOIN c ON c.a_id=a.id AND c.b_id=b.id > > > Any ideas on how to solve this? > > > -- Daniel Grace > > > > -- > 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+...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com<javascript:> > . > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > > > -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
