Re: [Sqlalchemy-tickets] [sqlalchemy] #2948: clarify behavior of explicit primaryjoin wrt backref, including importance of establishing both sides for unusual cases (was: generated sql when using custom primaryjoin and backref is funky.)

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

#2948: clarify behavior of explicit primaryjoin wrt backref, including importance
of establishing both sides for unusual cases
--------------------------------+----------------------------------
      Reporter:  mkadin         |      Owner:  zzzeek
          Type:  defect         |     Status:  new
      Priority:  medium         |  Milestone:  0.9.xx
     Component:  documentation  |   Severity:  minor - half an hour
    Resolution:                 |   Keywords:
Progress State:  in queue       |
--------------------------------+----------------------------------
Changes (by zzzeek):

 * component:  orm => documentation
 * severity:  no triage selected yet => minor - half an hour
 * status_field:  awaiting triage => in queue
 * milestone:   => 0.9.xx


Comment:

 While there is a simple way to make this work in what I think is an
 acceptable way, it has to occur on your end (basically, setting a distinct
 primaryjoin, which is what you did.  Set it to None instead of "", btw.).
 I don't think there's a way that SQLAlchemy could be guessing how to
 interpret this, and beyond that, lazy loading doesn't generate JOINs.
 You can produce lazy loads that have JOINs rendered, but the JOIN would
 always have to come from an existing target mapper which is mapped to a
 JOIN in some way or a special "secondary" target that refers to a JOIN;
 that is, within "produce a lazy load from A to B", no new "JOIN" is
 produced that wasn't already defined.   The complexity of lazyload having
 to decide that a particular primaryjoin implies a JOIN should be created,
 when meanwhile it's really not what you want anyway, isn't really worth it
 and would make life harder for everyone (surprising, buggy, complicated,
 etc., and not even what you want!)

 The key pattern behind why this produces bad SQL is because the
 relationship of Person.city is specifically omitting a City that has a
 "deleted_at" value.    So then if we say, "given a City, what is the
 collection of People for that City?".  OK, if City.deleted_at is None,
 this is simple, it is all the People rows with city_id=<city.id>.

 But if City.deleted_at is non NULL.  Is it:  A. all the Person objects
 which refer to this City via city_id?  B. no Person objects, because City
 is deleted?   or C. this is an invalid request; for a City with deleted_at
 is non NULL, the list of Person objects associated with it is undefined?

 It's because the behavior of this backref case is inherently ambiguous
 that happens to correspond with SQL that makes no sense either.

 We can add documentation clarifying the behavior of primaryjoin when used
 in a backref but that's about it here.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2948#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python