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 <[email protected]> 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: 42P01 > Hint: 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
