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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
