[Sqlalchemy-tickets] Issue #3544: from_self() with contains_eager (zzzeek/sqlalchemy)

[Lukas S. <iss...@bi...>]

New issue 3544: from_self() with contains_eager
https://bitbucket.org/zzzeek/sqlalchemy/issues/3544/from_self-with-contains_eager

Lukas Siemon:

The test case below generates the following query:

```
#!sql
SELECT
   label_alias.id,
   label_alias.name,
   anon_1.venue_id,
   anon_1.venue_name   
FROM
   label AS label_alias,  -- this should come from the inner query
   (SELECT
      venue.id AS venue_id,
      venue.name AS venue_name   
   FROM
      venue 
   JOIN
      venue_to_label AS venue_to_label_1 
         ON venue.id = venue_to_label_1.venue_id 
   JOIN
      label AS label_alias 
         ON label_alias.id = venue_to_label_1.label_id   
   WHERE
      label_alias.name IN (
         %(
            name_1
         )s
      )) AS anon_1
```

However, based on the alchemy query, I'd expect the label_alias to come from the inner sql query instead of from the outer. Is there an easy way to resolve this?

To give some background: We're using this strategy generically with joinedload instead of contains_eager to serialize all our queries to json. It works really well, however now the requirement came up to only return "certain joins from the joinedload".

I've tested the issue with
SQLAlchemy==1.0.8
Flask==0.10.1
Flask-SQLAlchemy==2.0

Test case:
```
#!python

import unittest
from sqlalchemy import (
    Table, Column, Integer, ForeignKey, String, func)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (relationship, aliased, contains_eager)
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy


# -- create all the database models
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = ("sqlite://")
db = SQLAlchemy(app)

Base = declarative_base()

venue_to_label = Table(
    'venue_to_label', db.metadata,
    Column('venue_id', Integer, ForeignKey('venue.id'), primary_key=True),
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True)
)


class Label(db.Model):
    __tablename__ = 'label'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(254))


class Venue(db.Model):
    __tablename__ = 'venue'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(254))
    labels = relationship(Label, secondary=venue_to_label)

db.drop_all()
db.create_all()


class TestContainsEager(unittest.TestCase):

    def test_contains_eager(self):
        query = Venue.query
        # filtered join
        label_alias = aliased(Label, name="label_alias")
        query = query.join(label_alias, Venue.labels)
        query = query.filter(label_alias.name.in_(["label1"]))

        # together with a windowing function this allows to correctly apply
        # limit and order to the query, needed since labels is a *-to-many join
        query = query.from_self()

        # define to load from the inner query
        query = query.options(
            contains_eager(
                'labels', alias=label_alias
            ).load_only('id', 'name')
        )

        # contains_eager does not pick up the alias from the inner query
        import sqlalchemy.dialects.postgresql as postgresql
        print query.statement.compile(dialect=postgresql.dialect())

```