<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">given issues like <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="384632180" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/3223" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/3223/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/3223">#3223</a> and the openstack issues with model_query(), here's a sketch of an idea:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="@event.listens_for(EventedQuery, &quot;before_compile&quot;, retval=True)
def append_order_by(new_query):
    
    if inspect(new_query).selects_against(MyClass).has_any():
        for insp in inspect(new_query).selects_against(MyClass).all_aliases():
            new_query = new_query.order_by(insp.entity.whatever)
    return new_query
"><pre class="notranslate"><code class="notranslate">@event.listens_for(EventedQuery, "before_compile", retval=True)
def append_order_by(new_query):
    
    if inspect(new_query).selects_against(MyClass).has_any():
        for insp in inspect(new_query).selects_against(MyClass).all_aliases():
            new_query = new_query.order_by(insp.entity.whatever)
    return new_query

</code></pre></div>
<p dir="auto">with this extension we'd need to identify specific use cases (since we see them all the time anyway) and add them to the docs of this extension as covered use cases.  So the docs here need lots of recipes and they need to be tested.</p>
<p dir="auto"><strong>Describe the bug</strong></p>
<p dir="auto">I have <code class="notranslate">Parent</code> and <code class="notranslate">Child</code> classes, with a one-to-many relationship. The <code class="notranslate">Child</code> class has a <code class="notranslate">backref</code> to the <code class="notranslate">Parent</code> called <code class="notranslate">_parent</code>. These objects often live outside of <code class="notranslate">SQLAlchemy</code>, so I need to maintain this reference myself sometimes.<br>
If I get a family from the DB, and then try to assign myself the <code class="notranslate">backref</code> parameter, the children objects are duplicated</p>
<p dir="auto"><strong>Expected behavior</strong></p>
<p dir="auto">This behaviour is new in <code class="notranslate">1.4.7</code> and still happens against the master branch of the repo.<br>
When running the example bellow with 1.4.7 and 1.4.6, the debug logs are perfectly identical.</p>
<p dir="auto"><strong>To Reproduce</strong></p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from __future__ import absolute_import
from __future__ import division
from __future__ import print_function

import logging

from sqlalchemy.orm import relationship, backref, sessionmaker, joinedload, mapper
from sqlalchemy import create_engine, Table, Column, MetaData, ForeignKey, \
    Integer, String


logging.basicConfig()
logging.getLogger(&quot;sqlalchemy.engine&quot;).setLevel(logging.DEBUG)


class Parent(object):
  &quot;&quot;&quot; Parent class, managing children &quot;&quot;&quot;

  def __init__(self):
    self.ParentName = None

  def _notify(self):
    for child in self.__children__:
      # PROBLEM HAPPENS HERE
      # When setting the _parent value once the
      # object was retrieved from the DB, so
      # in the del r[0] call,
      # the children ends up being duplicated
      child._parent = self

  def __contains__(self, child):
    return bool(child in self.__children__)

  def __iadd__(self, child):
    &quot;&quot;&quot; Add a Child to the list of children, and
        set the backref
    &quot;&quot;&quot;
    if child not in self:
      self.__children__.append(child)
      child._parent = self
      self._notify()
    return self

  def __delitem__(self, i):
    self.__children__.__delitem__(i)
    self._notify()

  def __len__(self):
    &quot;&quot;&quot; nb of subParents &quot;&quot;&quot;
    return len(self.__children__)


class Child(object):
  &quot;&quot;&quot; Dummy Child class, with backref to the parent &quot;&quot;&quot;

  def __init__(self, ChildName):
    self.ChildName = ChildName
    # backref
    self._parent = None

########################################################################


metadata = MetaData()

childTable = Table('Child', metadata,
                   Column('ChildName', String(64), nullable=False),
                   Column('ChildID', Integer, primary_key=True),
                   Column('ParentID', Integer,
                          ForeignKey('Parent.ParentID', ondelete='CASCADE'),
                          nullable=False),
                   mysql_engine='InnoDB')


mapper(Child, childTable)


parentTable = Table('Parent', metadata,
                    Column('ParentName', String(255), nullable=False),
                    Column('ParentID', Integer, primary_key=True),
                    mysql_engine='InnoDB')

mapper(Parent, parentTable, properties={'__children__': relationship(Child,
                                                                     backref=backref('_parent',
                                                                                     lazy='immediate'),
                                                                     lazy='immediate',
                                                                     passive_deletes=True,
                                                                     cascade=&quot;all, delete-orphan&quot;)})


########################################################################
class FamilyDB(object):

  def __init__(self):

    self.dbHost = 'mysql'
    self.dbPort = 3306
    self.dbUser = 'root'
    self.dbPass = 'password'
    self.dbName = 'Test'

    self.engine = create_engine('mysql://root:password@mysql:3306/Test')

    metadata.bind = self.engine

    self.DBSession = sessionmaker(bind=self.engine)
    metadata.create_all(self.engine)

  def putParent(self, parent):
    &quot;&quot;&quot; Put a parent and children in the DB &quot;&quot;&quot;

    session = self.DBSession(expire_on_commit=False)
    try:

      parent = session.merge(parent)
      session.add(parent)
      session.commit()
      session.expunge_all()

      return parent.ParentID

    except Exception:
      session.rollback()
      raise
    finally:
      session.close()

  def getParent(self, parentID):
    &quot;&quot;&quot;Get a parent from the DB with its children&quot;&quot;&quot;

    session = self.DBSession(expire_on_commit=False)

    try:
      parent = session.query(Parent) \
          .options(joinedload('__children__')) \
          .filter(Parent.ParentID == parentID)\
          .one()

      session.expunge_all()
      return parent

    except Exception:
      session.rollback()
      raise
    finally:
      session.close()


def deathScenario():

  db = FamilyDB()

  # Create my test data, a parent with 2 children
  p = Parent()
  p.ParentName = &quot;Father&quot;

  ch1 = Child(&quot;Child1&quot;)
  ch2 = Child(&quot;Child2&quot;)

  p += ch1
  p += ch2

  parentID = db.putParent(p)

  # Now retrieve that family from the DB
  p = db.getParent(parentID)

  # I expect here that the parent has 2 children
  assert len(p) == 2

  # Let's kill one child :-)
  del p[0]

  # I expect the parent to have a single child left
  # But here it fails, and has 2.
  # The same one is duplicated
  assert len(p) == 1, p.__children__


if __name__ == '__main__':
  deathScenario()"><pre class="notranslate"><span class="pl-k">from</span> __future__ <span class="pl-k">import</span> <span class="pl-s1">absolute_import</span>
<span class="pl-k">from</span> __future__ <span class="pl-k">import</span> <span class="pl-s1">division</span>
<span class="pl-k">from</span> __future__ <span class="pl-k">import</span> <span class="pl-s1">print_function</span>

<span class="pl-k">import</span> <span class="pl-s1">logging</span>

<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span> <span class="pl-k">import</span> <span class="pl-s1">relationship</span>, <span class="pl-s1">backref</span>, <span class="pl-s1">sessionmaker</span>, <span class="pl-s1">joinedload</span>, <span class="pl-s1">mapper</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">create_engine</span>, <span class="pl-v">Table</span>, <span class="pl-v">Column</span>, <span class="pl-v">MetaData</span>, <span class="pl-v">ForeignKey</span>, \
    <span class="pl-v">Integer</span>, <span class="pl-v">String</span>


<span class="pl-s1">logging</span>.<span class="pl-en">basicConfig</span>()
<span class="pl-s1">logging</span>.<span class="pl-en">getLogger</span>(<span class="pl-s">"sqlalchemy.engine"</span>).<span class="pl-en">setLevel</span>(<span class="pl-s1">logging</span>.<span class="pl-v">DEBUG</span>)


<span class="pl-k">class</span> <span class="pl-v">Parent</span>(<span class="pl-s1">object</span>):
  <span class="pl-s">""" Parent class, managing children """</span>

  <span class="pl-k">def</span> <span class="pl-en">__init__</span>(<span class="pl-s1">self</span>):
    <span class="pl-s1">self</span>.<span class="pl-v">ParentName</span> <span class="pl-c1">=</span> <span class="pl-c1">None</span>

  <span class="pl-k">def</span> <span class="pl-en">_notify</span>(<span class="pl-s1">self</span>):
    <span class="pl-k">for</span> <span class="pl-s1">child</span> <span class="pl-c1">in</span> <span class="pl-s1">self</span>.<span class="pl-s1">__children__</span>:
      <span class="pl-c"># PROBLEM HAPPENS HERE</span>
      <span class="pl-c"># When setting the _parent value once the</span>
      <span class="pl-c"># object was retrieved from the DB, so</span>
      <span class="pl-c"># in the del r[0] call,</span>
      <span class="pl-c"># the children ends up being duplicated</span>
      <span class="pl-s1">child</span>.<span class="pl-s1">_parent</span> <span class="pl-c1">=</span> <span class="pl-s1">self</span>

  <span class="pl-k">def</span> <span class="pl-en">__contains__</span>(<span class="pl-s1">self</span>, <span class="pl-s1">child</span>):
    <span class="pl-k">return</span> <span class="pl-en">bool</span>(<span class="pl-s1">child</span> <span class="pl-c1">in</span> <span class="pl-s1">self</span>.<span class="pl-s1">__children__</span>)

  <span class="pl-k">def</span> <span class="pl-en">__iadd__</span>(<span class="pl-s1">self</span>, <span class="pl-s1">child</span>):
    <span class="pl-s">""" Add a Child to the list of children, and</span>
<span class="pl-s">        set the backref</span>
<span class="pl-s">    """</span>
    <span class="pl-k">if</span> <span class="pl-s1">child</span> <span class="pl-c1">not</span> <span class="pl-c1">in</span> <span class="pl-s1">self</span>:
      <span class="pl-s1">self</span>.<span class="pl-s1">__children__</span>.<span class="pl-en">append</span>(<span class="pl-s1">child</span>)
      <span class="pl-s1">child</span>.<span class="pl-s1">_parent</span> <span class="pl-c1">=</span> <span class="pl-s1">self</span>
      <span class="pl-s1">self</span>.<span class="pl-en">_notify</span>()
    <span class="pl-k">return</span> <span class="pl-s1">self</span>

  <span class="pl-k">def</span> <span class="pl-en">__delitem__</span>(<span class="pl-s1">self</span>, <span class="pl-s1">i</span>):
    <span class="pl-s1">self</span>.<span class="pl-s1">__children__</span>.<span class="pl-en">__delitem__</span>(<span class="pl-s1">i</span>)
    <span class="pl-s1">self</span>.<span class="pl-en">_notify</span>()

  <span class="pl-k">def</span> <span class="pl-en">__len__</span>(<span class="pl-s1">self</span>):
    <span class="pl-s">""" nb of subParents """</span>
    <span class="pl-k">return</span> <span class="pl-en">len</span>(<span class="pl-s1">self</span>.<span class="pl-s1">__children__</span>)


<span class="pl-k">class</span> <span class="pl-v">Child</span>(<span class="pl-s1">object</span>):
  <span class="pl-s">""" Dummy Child class, with backref to the parent """</span>

  <span class="pl-k">def</span> <span class="pl-en">__init__</span>(<span class="pl-s1">self</span>, <span class="pl-v">ChildName</span>):
    <span class="pl-s1">self</span>.<span class="pl-v">ChildName</span> <span class="pl-c1">=</span> <span class="pl-v">ChildName</span>
    <span class="pl-c"># backref</span>
    <span class="pl-s1">self</span>.<span class="pl-s1">_parent</span> <span class="pl-c1">=</span> <span class="pl-c1">None</span>

<span class="pl-c">########################################################################</span>


<span class="pl-s1">metadata</span> <span class="pl-c1">=</span> <span class="pl-v">MetaData</span>()

<span class="pl-s1">childTable</span> <span class="pl-c1">=</span> <span class="pl-v">Table</span>(<span class="pl-s">'Child'</span>, <span class="pl-s1">metadata</span>,
                   <span class="pl-v">Column</span>(<span class="pl-s">'ChildName'</span>, <span class="pl-v">String</span>(<span class="pl-c1">64</span>), <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>),
                   <span class="pl-v">Column</span>(<span class="pl-s">'ChildID'</span>, <span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>),
                   <span class="pl-v">Column</span>(<span class="pl-s">'ParentID'</span>, <span class="pl-v">Integer</span>,
                          <span class="pl-v">ForeignKey</span>(<span class="pl-s">'Parent.ParentID'</span>, <span class="pl-s1">ondelete</span><span class="pl-c1">=</span><span class="pl-s">'CASCADE'</span>),
                          <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>),
                   <span class="pl-s1">mysql_engine</span><span class="pl-c1">=</span><span class="pl-s">'InnoDB'</span>)


<span class="pl-en">mapper</span>(<span class="pl-v">Child</span>, <span class="pl-s1">childTable</span>)


<span class="pl-s1">parentTable</span> <span class="pl-c1">=</span> <span class="pl-v">Table</span>(<span class="pl-s">'Parent'</span>, <span class="pl-s1">metadata</span>,
                    <span class="pl-v">Column</span>(<span class="pl-s">'ParentName'</span>, <span class="pl-v">String</span>(<span class="pl-c1">255</span>), <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>),
                    <span class="pl-v">Column</span>(<span class="pl-s">'ParentID'</span>, <span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>),
                    <span class="pl-s1">mysql_engine</span><span class="pl-c1">=</span><span class="pl-s">'InnoDB'</span>)

<span class="pl-en">mapper</span>(<span class="pl-v">Parent</span>, <span class="pl-s1">parentTable</span>, <span class="pl-s1">properties</span><span class="pl-c1">=</span>{<span class="pl-s">'__children__'</span>: <span class="pl-en">relationship</span>(<span class="pl-v">Child</span>,
                                                                     <span class="pl-s1">backref</span><span class="pl-c1">=</span><span class="pl-en">backref</span>(<span class="pl-s">'_parent'</span>,
                                                                                     <span class="pl-s1">lazy</span><span class="pl-c1">=</span><span class="pl-s">'immediate'</span>),
                                                                     <span class="pl-s1">lazy</span><span class="pl-c1">=</span><span class="pl-s">'immediate'</span>,
                                                                     <span class="pl-s1">passive_deletes</span><span class="pl-c1">=</span><span class="pl-c1">True</span>,
                                                                     <span class="pl-s1">cascade</span><span class="pl-c1">=</span><span class="pl-s">"all, delete-orphan"</span>)})


<span class="pl-c">########################################################################</span>
<span class="pl-k">class</span> <span class="pl-v">FamilyDB</span>(<span class="pl-s1">object</span>):

  <span class="pl-k">def</span> <span class="pl-en">__init__</span>(<span class="pl-s1">self</span>):

    <span class="pl-s1">self</span>.<span class="pl-s1">dbHost</span> <span class="pl-c1">=</span> <span class="pl-s">'mysql'</span>
    <span class="pl-s1">self</span>.<span class="pl-s1">dbPort</span> <span class="pl-c1">=</span> <span class="pl-c1">3306</span>
    <span class="pl-s1">self</span>.<span class="pl-s1">dbUser</span> <span class="pl-c1">=</span> <span class="pl-s">'root'</span>
    <span class="pl-s1">self</span>.<span class="pl-s1">dbPass</span> <span class="pl-c1">=</span> <span class="pl-s">'password'</span>
    <span class="pl-s1">self</span>.<span class="pl-s1">dbName</span> <span class="pl-c1">=</span> <span class="pl-s">'Test'</span>

    <span class="pl-s1">self</span>.<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">'mysql://root:password@mysql:3306/Test'</span>)

    <span class="pl-s1">metadata</span>.<span class="pl-s1">bind</span> <span class="pl-c1">=</span> <span class="pl-s1">self</span>.<span class="pl-s1">engine</span>

    <span class="pl-s1">self</span>.<span class="pl-v">DBSession</span> <span class="pl-c1">=</span> <span class="pl-en">sessionmaker</span>(<span class="pl-s1">bind</span><span class="pl-c1">=</span><span class="pl-s1">self</span>.<span class="pl-s1">engine</span>)
    <span class="pl-s1">metadata</span>.<span class="pl-en">create_all</span>(<span class="pl-s1">self</span>.<span class="pl-s1">engine</span>)

  <span class="pl-k">def</span> <span class="pl-en">putParent</span>(<span class="pl-s1">self</span>, <span class="pl-s1">parent</span>):
    <span class="pl-s">""" Put a parent and children in the DB """</span>

    <span class="pl-s1">session</span> <span class="pl-c1">=</span> <span class="pl-s1">self</span>.<span class="pl-v">DBSession</span>(<span class="pl-s1">expire_on_commit</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)
    <span class="pl-k">try</span>:

      <span class="pl-s1">parent</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">merge</span>(<span class="pl-s1">parent</span>)
      <span class="pl-s1">session</span>.<span class="pl-en">add</span>(<span class="pl-s1">parent</span>)
      <span class="pl-s1">session</span>.<span class="pl-en">commit</span>()
      <span class="pl-s1">session</span>.<span class="pl-en">expunge_all</span>()

      <span class="pl-k">return</span> <span class="pl-s1">parent</span>.<span class="pl-v">ParentID</span>

    <span class="pl-k">except</span> <span class="pl-v">Exception</span>:
      <span class="pl-s1">session</span>.<span class="pl-en">rollback</span>()
      <span class="pl-k">raise</span>
    <span class="pl-k">finally</span>:
      <span class="pl-s1">session</span>.<span class="pl-en">close</span>()

  <span class="pl-k">def</span> <span class="pl-en">getParent</span>(<span class="pl-s1">self</span>, <span class="pl-s1">parentID</span>):
    <span class="pl-s">"""Get a parent from the DB with its children"""</span>

    <span class="pl-s1">session</span> <span class="pl-c1">=</span> <span class="pl-s1">self</span>.<span class="pl-v">DBSession</span>(<span class="pl-s1">expire_on_commit</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)

    <span class="pl-k">try</span>:
      <span class="pl-s1">parent</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-v">Parent</span>) \
          .<span class="pl-en">options</span>(<span class="pl-en">joinedload</span>(<span class="pl-s">'__children__'</span>)) \
          .<span class="pl-en">filter</span>(<span class="pl-v">Parent</span>.<span class="pl-v">ParentID</span> <span class="pl-c1">==</span> <span class="pl-s1">parentID</span>)\
          .<span class="pl-en">one</span>()

      <span class="pl-s1">session</span>.<span class="pl-en">expunge_all</span>()
      <span class="pl-k">return</span> <span class="pl-s1">parent</span>

    <span class="pl-k">except</span> <span class="pl-v">Exception</span>:
      <span class="pl-s1">session</span>.<span class="pl-en">rollback</span>()
      <span class="pl-k">raise</span>
    <span class="pl-k">finally</span>:
      <span class="pl-s1">session</span>.<span class="pl-en">close</span>()


<span class="pl-k">def</span> <span class="pl-en">deathScenario</span>():

  <span class="pl-s1">db</span> <span class="pl-c1">=</span> <span class="pl-v">FamilyDB</span>()

  <span class="pl-c"># Create my test data, a parent with 2 children</span>
  <span class="pl-s1">p</span> <span class="pl-c1">=</span> <span class="pl-v">Parent</span>()
  <span class="pl-s1">p</span>.<span class="pl-v">ParentName</span> <span class="pl-c1">=</span> <span class="pl-s">"Father"</span>

  <span class="pl-s1">ch1</span> <span class="pl-c1">=</span> <span class="pl-v">Child</span>(<span class="pl-s">"Child1"</span>)
  <span class="pl-s1">ch2</span> <span class="pl-c1">=</span> <span class="pl-v">Child</span>(<span class="pl-s">"Child2"</span>)

  <span class="pl-s1">p</span> <span class="pl-c1">+=</span> <span class="pl-s1">ch1</span>
  <span class="pl-s1">p</span> <span class="pl-c1">+=</span> <span class="pl-s1">ch2</span>

  <span class="pl-s1">parentID</span> <span class="pl-c1">=</span> <span class="pl-s1">db</span>.<span class="pl-en">putParent</span>(<span class="pl-s1">p</span>)

  <span class="pl-c"># Now retrieve that family from the DB</span>
  <span class="pl-s1">p</span> <span class="pl-c1">=</span> <span class="pl-s1">db</span>.<span class="pl-en">getParent</span>(<span class="pl-s1">parentID</span>)

  <span class="pl-c"># I expect here that the parent has 2 children</span>
  <span class="pl-k">assert</span> <span class="pl-en">len</span>(<span class="pl-s1">p</span>) <span class="pl-c1">==</span> <span class="pl-c1">2</span>

  <span class="pl-c"># Let's kill one child :-)</span>
  <span class="pl-k">del</span> <span class="pl-s1">p</span>[<span class="pl-c1">0</span>]

  <span class="pl-c"># I expect the parent to have a single child left</span>
  <span class="pl-c"># But here it fails, and has 2.</span>
  <span class="pl-c"># The same one is duplicated</span>
  <span class="pl-k">assert</span> <span class="pl-en">len</span>(<span class="pl-s1">p</span>) <span class="pl-c1">==</span> <span class="pl-c1">1</span>, <span class="pl-s1">p</span>.<span class="pl-s1">__children__</span>


<span class="pl-k">if</span> <span class="pl-s1">__name__</span> <span class="pl-c1">==</span> <span class="pl-s">'__main__'</span>:
  <span class="pl-en">deathScenario</span>()</pre></div>
<p dir="auto"><strong>Error</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;/tmp/reproducer/reproducer2.py&quot;, line 181, in &lt;module&gt;
    deathScenario()
  File &quot;/tmp/reproducer/reproducer2.py&quot;, line 177, in deathScenario
    assert len(p) == 1, p.__children__
AssertionError: [&lt;__main__.Child object at 0x7fb7e541e590&gt;, &lt;__main__.Child object at 0x7fb7e541e590&gt;]"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "/tmp/reproducer/reproducer2.py", line 181, in &lt;module&gt;
    deathScenario()
  File "/tmp/reproducer/reproducer2.py", line 177, in deathScenario
    assert len(p) == 1, p.__children__
AssertionError: [&lt;__main__.Child object at 0x7fb7e541e590&gt;, &lt;__main__.Child object at 0x7fb7e541e590&gt;]
</code></pre></div>
<p dir="auto"><strong>Versions.</strong></p>
<ul dir="auto">
<li>OS: CentOS Linux release 7.8.2003</li>
<li>Python: 2.7.13</li>
<li>SQLAlchemy: from 1.4.7 onward</li>
<li>Database: Mysql</li>
<li>DBAPI: MySQL-python 1.2.5</li>
</ul>
<p dir="auto"><strong>Have a nice day!</strong></p>