no, the SQL is SELECT ... FROM x JOIN y ON x.a=y.a AND x.b=y.b AND x.c=y.c
On Apr 21, 2014, at 9:02 AM, Rick Otten <rottenwindf...@gmail.com> wrote: > Interesting, and I had thought about it, but wouldn't that run the subquery > (myselect) 3 times for each join? > > > On Sunday, April 20, 2014 10:01:20 PM UTC-4, Michael Bayer wrote: > there's a way to get the tuple comparison to work here though it's not as > common, I'd have to try it out and there might be some quirks to work out. I > would think this would be more straightforward using a straight AND: > > and_(mo.c.col1 == myselect.c.col1, mo.c.col2 == myselect.c.col2, mo.c.col3 == > myselect.c.col3) > > where "myselect" is like: select([...]).where(...).alias() > > > On Apr 20, 2014, at 9:33 PM, Rick Otten <rottenw...@gmail.com> wrote: > >> So FWIW, for now, I cast all three columns into strings and concatenated >> them using the column_property() expression in the mapper, and then did the >> comparison on that. It worked ok to create the join condition I needed. It >> wasn't a pretty as a tuple comparison but it got the job done and I can hit >> my deadline for tomorrow morning. >> >> >> On Sunday, April 20, 2014 2:38:21 PM UTC-4, Rick Otten wrote: >> I would like to apply a condition in an outer join that matches a multi-item >> list rather than a single value. >> >> In other words, I'm trying to get SQLAlchemy 0.8.6 connecting to PostgreSQL >> 9.3.4 to generate SQL like this: >> >> >> >> select >> * >> from >> mytable mt >> left outer join myothertable mo on >> (m0.col1, m0.col2, mo.col3) = (select m2.col1, m2.col2, m2.col3 >> from myothertable m2 >> where mt.col1 = >> m2.wilson_id >> order by m2.col3 >> desc >> limit 1) >> >> >> >> When I construct my query object back in Python 2.7: >> >> mo = aliased(myothertable) >> m2 = aliased(myothertable) >> mySession.query(mytable, myothertable)\ >> .select_from(mytable)\ >> .outerjoin(mo, >> [mo.col1, mo.col2, mo.col3] == select(\ >> [m2.col1, m2.col2, m2.col3])\ >> .where(m2.col1 == mytable.col1)\ >> .order_by(m2.col3.desc())\ >> .limit(1)) >> >> >> I get: >> >> File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 1699, in >> outerjoin >> File "<string>", line 1, in <lambda> >> File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 51, in >> generate >> File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 1816, in >> _join >> File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 1846, in >> _join_left_to_right >> File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 1861, in >> _prepare_right_side >> File "build/bdist.linux-x86_64/egg/sqlalchemy/inspection.py", line 74, in >> inspect >> sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for >> object of type <type 'bool'> >> >> I tried both List type and Tuple type in my comparison from the inner select >> statement. >> When I look at the code where this exception is thrown, it suggests that >> something is wrong with my mapping. >> >> I've done this join by comparing a single column with these two tables, and >> I've done similar things with other single column comparisons. I'm not sure >> why I'm unable to compare a set of columns instead. >> >> I've been pondering casting all three columns to string and then >> concatenating them (either in the table mapping, or in the query) to see if >> I can get back to a single value comparision which I expect to work. I'd >> rather just compare the tuple/lists though. >> >> I could use some hints. If you guys have some. Thanks! >> >> >> >> >> >> -- >> 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. >> To post to this group, send email to sqlal...@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. > > > -- > 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. > For more options, visit https://groups.google.com/d/optout. -- 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. For more options, visit https://groups.google.com/d/optout.
