SQLAlchemy - connection condition fails with AttributeError: neither the BinaryExpression object nor the 'Comparator' object have the 'selectable' attribute

Question

I am using SQLAlchemy with Pyramid. I am trying to execute a query with a custom join condition:

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

However, the request cannot complete the following error:

AttributeError: Neither the BinaryExpression object nor the Comparator object have a select attribute

The problem stems from the condition, as if I delete it, the request works:

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

I do not 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 is happening?

Answer 1

, . , .outerjoin(A.b, ...), .outerjoin(B, ...)

,

( )

Answer 2

, .outerjoin(Ab.., , .

Answer 3

- ON join(): ON . , ON, and_/or_. , ON :

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() API SQLA , ( , join(*args, **kwargs) ). 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)

, :

• with an explicit clause ON and Aand Bcan be not only display classes, but also any "selectable":, subquery()instance, Tableor alias ( aliased(selectable)).
• without an explicit ON clause Aand Bcan only be a display class or instanceTable