CODEWITHSUNDEEP
pythonsqlalchemypyramid
Eino Gourdin
Eino Gourdin

Reputation: 4547

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

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 ?

Upvotes: 14

Views: 11712

Answers (4)

DINA TAKLIT
DINA TAKLIT

Reputation: 8418

For me I got this issue once I tried to PATCH a row in my db, I took the code directly from the SqlAlchemy2 doc and I tried to adapt it

from sqlalchemy import update as sqlalchemy_update

query = sqlalchemy_update(DimensionsEvaluation).where(
                DimensionsEvaluation.id == id).values(DimensionsEvaluation.selected == True)

but I got this error:

Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'items'

to fix this I had simply to use dictionary for values like so

    query = sqlalchemy_update(DimensionsEvaluation).where(
                DimensionsEvaluation.id == id).values({
                    "selected": True
                })

check this link for more details https://www.geeksforgeeks.org/sqlalchemy-core-update-statement/

Upvotes: 0

Tim
Tim

Reputation: 13078

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

Upvotes: 4

naomiajacobs
naomiajacobs

Reputation: 1

Another way of saying this is that if you are already specifying the relationship via .outerjoin(A.b..., you no longer need to specify the condition, and in fact cannot do both.

Upvotes: 0

Eino Gourdin
Eino Gourdin

Reputation: 4547

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)

Upvotes: 25

Related Questions