[Sqlalchemy-tickets] [sqlalchemy] #2948: generated sql when using custom primaryjoin and backref is
Brought to you by:
zzzeek
|
From: sqlalchemy <mi...@zz...> - 2014-02-09 17:08:35
|
#2948: generated sql when using custom primaryjoin and backref is funky.
--------------------+-----------------------------------------
Reporter: mkadin | Owner: zzzeek
Type: defect | Status: new
Priority: medium | Milestone:
Component: orm | Severity: no triage selected yet
Keywords: | Progress State: awaiting triage
--------------------+-----------------------------------------
Hello,
In searching through some of our slowest queries, we came across some
funky behavior with the sql that is generated with a relationship that
included a custom primaryjoin and a backref. I've attached a script,
running against the latest sqlalchemy from pip, which will reproduce the
issue on a postgres backend; I haven't tried any other DBs.
For a relationship that looks like
{{{
city = relationship('City', primaryjoin='and_(City.id == Person.city_id,
City.deleted_at == None)', backref='people')
}}}
The sql generated for accessing this relationship is fine. However, for
the backref, you would expect the sql to look like
{{{
SELECT persons.id, persons.name, persons.city_id FROM persons
JOIN cities on persons.city_id = cities.id AND cities.deleted at IS NULL
WHERE cities.id = %(some_city_id);
}}}
Though is is strange to have the {{{cities.deleted_at IS NULL}}} in the
join clause, it is still valid sql no? Instead, sqlalchemy gives the
following sql:
{{{
SELECT persons.id AS persons_id, persons.name AS persons_name,
persons.city_id AS persons_city_id
FROM persons, cities
WHERE %(param_1)s = persons.city_id AND cities.deleted_at IS NULL
}}}
This ends up scanning the entire cities table even though no info is
needed.
For now, we've used the backref() function to define a specific
primaryjoin="" for the backref, which is fine, but I thought I'd report
this as a bug as well.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2948>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python
|