Issue 1:
Migrated issue, originally created by Adrian (@thiefmaster)

from sqlalchemy import *
from sqlalchemy.dialects.postgresql import ARRAY, array
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *

def _patch_sa_cte_fix():
import pkg_resources
sa_dist = pkg_resources.get_distribution('sqlalchemy')
if sa_dist.parsed_version > pkg_resources.parse_version('1.0.12'):
raise Exception('Remove this monkeypatch; SQLAlchemy contains the CTE fix')
from sqlalchemy.sql.selectable import CTE
from sqlalchemy.sql.elements import _clone
def _copy_internals(self, clone=_clone, **kw):
super(CTE, self)._copy_internals(clone, **kw)
if self._cte_alias is not None:
self._cte_alias = self
self._restates = frozenset([clone(elem, **kw) for elem in self._restates])
CTE._copy_internals = _copy_internals

_patch_sa_cte_fix()
Base = declarative_base()

class Category(Base):
__tablename__ = 'categories'
id = Column(Integer, primary_key=True)
parent_id = Column(
Integer,
ForeignKey('categories.id'),
index=True,
nullable=True
)
is_deleted = Column(
Boolean,
nullable=False,
default=False
)
children = relationship(
'Category',
primaryjoin=(id == remote(parent_id)) & ~remote(is_deleted),
lazy=True,
backref=backref(
'parent',
primaryjoin=(remote(id) == parent_id),
lazy=True
)
)

@staticmethod
def _create_column_prop():
cat_alias = aliased(Category)
cte_query = (select([cat_alias.id, cast(array([]), ARRAY(Integer)).label('parents')])
.where(cat_alias.parent_id.is_(None) & ~cat_alias.is_deleted)
.cte(recursive=True))
parent_query = (select([cat_alias.id, cte_query.c.parents.op('||')(cat_alias.parent_id)])
.where((cat_alias.parent_id == cte_query.c.id) & ~cat_alias.is_deleted))
cte_query = cte_query.union_all(parent_query)
query = select([func.count()]).where(cte_query.c.parents.contains(array([Category.id])))
Category.deep_children_count = column_property(query, deferred=True)

Category._create_column_prop()

e = create_engine('postgresql:///test', echo=True)
Base.metadata.create_all(e)
s = Session(e)

root = Category(id=0, children=[
Category(id=1),
Category(id=2, children=[
Category(id=3),
Category(id=4),
Category(id=5, children=[
Category(id=6),
Category(id=7)
])
])
])
s.add(root)
s.flush()
s.expire_all()

print

cat = s.query(Category).options(undefer('deep_children_count'),
joinedload('children').undefer('deep_children_count')).get(2)
print cat
print cat.deep_children_count
print cat.children

sqlalchemy.exc.CompileError: Multiple, unrelated CTEs found with the same name: u'anon_2'

Removing one of the undefers or using subqueryload instead of joinedload works, but it still looks like a bug to me.

Full traceback:

Traceback (most recent call last):
File "satest.py", line 90, in <module>
joinedload('children').undefer('deep_children_count')).get(2)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 831, in get
return self._get_impl(ident, loading.load_on_ident)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 864, in _get_impl
return fallback_fn(self, key)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 219, in load_on_ident
return q.one()
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2693, in one
ret = list(self)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2736, in __iter__
return self._execute_and_instances(context)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2751, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
return meth(self, multiparams, params)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1003, in _execute_clauseelement
inline=len(distilled_params) > 1)
File "<string>", line 1, in <lambda>
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 494, in compile
return self._compiler(dialect, bind=bind, **kw)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 500, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 395, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 190, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 213, in process
return obj._compiler_dispatch(self, **kwargs)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1585, in visit_select
for name, column in select._columns_plus_names
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1357, in _label_select_column
**column_clause_args
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 93, in _compiler_dispatch
return meth(self, **kw)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 615, in visit_label
OPERATORS[operators.as_] + \
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 93, in _compiler_dispatch
return meth(self, **kw)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 527, in visit_grouping
return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")"
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1608, in visit_select
text, select, inner_columns, froms, byfrom, kwargs)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1700, in _compose_select_body
for f in froms])
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1194, in visit_cte
cte_name)
sqlalchemy.exc.CompileError: Multiple, unrelated CTEs found with the same name: u'anon_2'

Issue 2:
Migrated issue, originally created by Henrique Alves (@hcarvalhoalves)

I'm trying to do something that, in principle, should be simple: inject a expression-based column into an ORM-based Query. I've tried this:

query = db.session.query(MyClass).all()
# Using `now` just as an example, ideally it's something that takes a parameter
# so it can't be replaced by `orm.column_property`
some_expression = func.now()
selectable = query.selectable
# The following works as expected, injecting the expression as a column
# the AnnotatedSelect object...
selectable.append_column(some_expression.label('some_column'))
# ... but I can't set it back on the query
query.selectable = selectable
query = query.order_by('some_column')

It fails with AttributeError since query.selectable is read-only. I haven't found another way to inject an expression by reading the docs or the source.

Query.add_column isn't quite the same thing since you now get a KeyedTuple back, not a MyClass object with the injected attribute.

orm.column_property is of limited usefulness since you can't pass arguments only known at runtime instead of class-evaluation time.

I thought SQLAlchemy made things like this a breeze, but I've spent days without finding an adequate solution. Either I'm missing something, or an API for this is badly needed.