CODEWITHSUNDEEP
pythonmysqlsqljoinsqlalchemy
Souad
Souad

Reputation: 5094

Join query in SqlAlchemy

I have this query in MySQL:

SELECT
    O.id,
    O.label,
    A.name
FROM
    organizations O
JOIN activities_area A JOIN assoc_organization_activities S ON
    O.identifier = S.organization_id AND A.identifier = S.activities_area_id
ORDER BY
    O.label

I'm trying to make it with SQLAlchemy:

A = aliased(model.Activities, name='A')
S = aliased(model.AssocOrganizationsActivities, name='S')
O = aliased(model.Organizations, name='O')

organizations_query = session.query(O.id, O.label, A.name) \
                             .join(A) \
                             .join(S) \
                             .filter(O.identifier == S.organization_id) \
                             .filter(A.identifier == S.activities_area_id) \
                             .order_by(O.label) \
                             .all()

But that gives me this error:

InvalidRequestError: Could not find a FROM clause to join from. Tried joining to <class 'model.model.Activities'>, but got: Can't find any foreign key relationships between 'organizations' and 'activities_area'.

The table AssocOrganizationsActivities has only 3 fields: the id, the id of the organization as a foreign key and the id of the activity area as a foreign key. So both tables organizations and activities are linked by AssocOrganizationsActivities.

How to get it work?

Upvotes: 0

Views: 475

Answers (1)

Ilja Everil&#228;
Ilja Everil&#228;

Reputation: 53017

Swap the order of the joins. Both your raw SQL and the query suffer from the same, so instead:

organizations_query = session.query(O.id, ...) \    
    .join(S) \
    .join(A) \
    ...

The WHERE clauses you're adding in

...
    .filter(O.identifier == S.organization_id) \
    .filter(A.identifier == S.activities_area_id) \
    ...

look like you're trying to use the pre ANSI join syntax, but mixed with ANSI joins. They're entirely redundant in that SQLAlchemy can deduce the ON clauses for the joins based on the foreign key relationships between the models. The raw SQL is broken in a similar manner in that

FROM
    organizations O
JOIN activities_area A JOIN ...

has no ON clause between O and A at all, in addition to that there's no obvious way to join them without first joining the association table. All in all:

organizations_query = session.query(O.id, O.label, A.name) \
    .join(S) \
    .join(A) \
    .order_by(O.label) \
    .all()

Upvotes: 1

Related Questions