SQLAlchemy - condition on join fails with AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'selectable'

Question

I'm running SQLAlchemy with Pyramid. I'm trying to run a query with a custom 'join' condition :

DBSession.query(A)\
        .outerjoin(A.b, B.a_id == A.id)\
        .all()

however the query fails the following error :

AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'selectable'

The problem stems from the condition, as if I remove it, the query works :

DBSession.query(A)\
        .outerjoin(A.b)\
        .all()

I don't understand the problem, as I follow the syntax described in the documentation :

q = session.query(User).join(Address, User.id==Address.user_id)

Does anyone see what's going on ?

Answer 1

Ok I saw it. If you add a custom condition, the syntax is not .outerjoin(A.b, ...), but rather .outerjoin(B, ...)

They should accept both, really

(and the error message could be a little more explicit)

Answer 2

Another possible reason for this error is incorrect usage of explicit ON clause for the join(): explicit ON clause should be a single expression. So, if your intention is to use multiple filters in the ON clause, they should be combined with and_/or_. E.g., if you want to have an additional condition in the ON clause for the join:

query(A).join(B, A.b_id = B.id, A.x > N)  # WRONG!
query(A).join(B, and_(A.b_id = B.id, A.x > N))  # CORRECT

---

Query.join() SQLA API doc is very detailed itself, but somewhat vague in the summary (it says it is join(*args, **kwargs) which doesn't help a lot). Here is the summary of some of the correct possible uses for the Query.join():

# declare the join using own field which leads to the related object:
query(A).join(A.b)


# declare the join using a class of the related mapper:
query(A).join(B)


# same as above (using related mapper class) but use explicit ON clause
# ON clause can be any/"complex" expression
query(A).join(B, A.b_id = B.id)
query(A).join(B, _and(A.b_id = B.id, ...))


# reverse the order of the join (useful to do a right outer join for example):
query(A).select_entity_from(B).join(A, isouter=True)

In all examples above except for the first one:

• with an explicit ON clause both A and B can be not only mapper classes, but anything "selectable": subquery(), an instance of Table or an alias (aliased(selectable)) will do.
• without explicit ON clause A and B can be only a mapper class or a Table instance