[Sqlalchemy-tickets] Issue #3544: from_self() with contains_eager (zzzeek/sqlalchemy)
Brought to you by:
zzzeek
|
From: Lukas S. <iss...@bi...> - 2015-09-29 18:35:49
|
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()) ``` |