[Sqlalchemy-tickets] [sqlalchemy] #2948: generated sql when using custom primaryjoin and backref is funky.

[sqlalchemy <mi...@zz...>]

#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