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

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)

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