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 [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.
