Issue 1:
Migrated issue, originally created by Michael Bayer (@zzzeek)

given issues like #3223 and the openstack issues with model_query(), here's a sketch of an idea:

@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

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.

Issue 2:
Describe the bug

I have Parent and Child classes, with a one-to-many relationship. The Child class has a backref to the Parent called _parent. These objects often live outside of SQLAlchemy, so I need to maintain this reference myself sometimes.

If I get a family from the DB, and then try to assign myself the backref parameter, the children objects are duplicated

Expected behavior

This behaviour is new in 1.4.7 and still happens against the master branch of the repo.

When running the example bellow with 1.4.7 and 1.4.6, the debug logs are perfectly identical.

To Reproduce

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("sqlalchemy.engine").setLevel(logging.DEBUG)

class Parent(object):
""" Parent class, managing children """

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):
""" Add a Child to the list of children, and
set the backref
"""
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):
""" nb of subParents """
return len(self.__children__)

class Child(object):
""" Dummy Child class, with backref to the parent """

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="all, delete-orphan")})

########################################################################
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):
""" Put a parent and children in the DB """

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):
"""Get a parent from the DB with its children"""

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 = "Father"

ch1 = Child("Child1")
ch2 = Child("Child2")

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()

Error

Traceback (most recent call last):
File "/tmp/reproducer/reproducer2.py", line 181, in <module>
deathScenario()
File "/tmp/reproducer/reproducer2.py", line 177, in deathScenario
assert len(p) == 1, p.__children__
AssertionError: [<__main__.Child object at 0x7fb7e541e590>, <__main__.Child object at 0x7fb7e541e590>]

Versions.

OS: CentOS Linux release 7.8.2003

Python: 2.7.13

SQLAlchemy: from 1.4.7 onward

Database: Mysql

DBAPI: MySQL-python 1.2.5

Have a nice day!