CODEWITHSUNDEEP
pythonmysqlormsqlalchemy
user3318421
user3318421

Reputation: 101

SQLAlchemy 1.0.0 add 'AND NULL' to join where no foreign key is defined

I'm building a query in Python using SQLAlchemy version 1.0.0 over MySQL server. We don’t have any foreign keys defined in the schemas.

I want to construct the following query:

select s.visit_date, ss.store_number_1, count(p.pk) num_of_probes
from probedata.session s
inner join probedata.scene ps
on s.session_uid = ps.session_uid
inner join probedata.probe p
on ps.pk = p.scene_fk
inner join static.stores ss
on s.store_fk = ss.pk
where s.status = 'Completed'
group by s.visit_date, ss.store_number_1

The code for the query is:

 num_of_probes = session.query(StoreSession.visit_date, Stores.store_number_1, func.count(Probe.pk).label('num_of_probes')) \
            .select_from(StoreSession) \
            .join(Scene, StoreSession.session_uid == Scene.session_uid) \
            .join(Probe, Scene.pk ==Probe.scene_fk) \
            .join(Stores, StoreSession.store_fk == Stores.pk) \
            .filter(StoreSession.status == 'Completed') \
            .group_by(StoreSession.visit_date, Stores.store_number_1)

The query I'm receiving is:

SELECT probedata.session.visit_date AS probedata_session_visit_date, static.stores.store_number_1 AS static_stores_store_number_1, count(probedata.probe.pk) AS num_of_probes
FROM probedata.session
JOIN probedata.scene
ON probedata.session.session_uid = probedata.scene.session_uid AND NULL
JOIN probedata.probe ON probedata.scene.pk = probedata.probe.scene_fk AND NULL
JOIN static.stores ON probedata.session.store_fk = static.stores.pk AND NULL
WHERE probedata.session.status = :status_1
GROUP BY probedata.session.visit_date, static.stores.store_number_1

The problem is that the SQLAlchemy join adds "AND NULL" to each of the joins in the SQL query.

I understood that it does it since no foreign keys are defined, and SqlAlcemy 0.9.8 doesn't add it.

How can I dismiss the 'AND NULL' condition in 1.0.0?

Upvotes: 0

Views: 1515

Answers (1)

oria general
oria general

Reputation: 273

Relevant information about the issue: Append AND NULL condition on joins

Upvotes: 1

Related Questions