Hi,
I'm attempting to load some filtered relationships, and am running into
issues when trying to independently load the same relationship multiple
times.
As an example, I'd like to load Album.tracks, and Album.tracks.album.tracks
in different ways. In the first tracks relationship, I'm trying to include
all tracks with track_id >= 4, and in the second relationship every trackId
< 4.
Is this possible? And if so, what load options or technique should I be
using? The query generated below contains all the information I'm trying to
get, it's a matter of loading that information into my models that's
tripping me up.
Complete/runnable example can be found attached or below:
from sqlalchemy import create_engine
from sqlalchemy.orm import aliased, sessionmaker, relationship,
contains_eager
from sqlalchemy import Column, ForeignKey, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
metadata = Base.metadata
class Album(Base):
__tablename__ = 'Album'
album_id = Column("AlbumId", Integer, primary_key=True)
title = Column("Title", Unicode(160), nullable=False)
class Track(Base):
__tablename__ = 'Track'
track_id = Column("TrackId", Integer, primary_key=True)
name = Column("Name", Unicode(200), nullable=False)
album_id = Column("AlbumId", ForeignKey('Album.AlbumId'), index=True)
album = relationship('Album', backref="tracks")
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine, checkfirst=True)
session_cls = sessionmaker(bind=engine)
session = session_cls()
# Set up the database with Chinook data
session.add(Album(album_id=1, title="For Those About To Rock We Salute
You"))
session.add(Album(album_id=2, title="Balls to the Wall"))
session.add(Album(album_id=3, title="Restless and Wild"))
session.add(Album(album_id=4, title="Let There Be Rock"))
session.add(Track(track_id=1, name="For Those About To Rock", album_id=1))
session.add(Track(track_id=2, name="Balls to the Wall", album_id=2))
session.add(Track(track_id=3, name="Fast As a Shark", album_id=3))
session.add(Track(track_id=4, name="Restless and Wild", album_id=3))
session.add(Track(track_id=5, name="Princess of the Dawn", album_id=3))
session.add(Track(track_id=6, name="Put The Finger On You", album_id=1))
session.add(Track(track_id=7, name="Let's Get It Up", album_id=1))
session.add(Track(track_id=8, name="Inject The Venom", album_id=1))
session.add(Track(track_id=9, name="Snowballed", album_id=1))
session.add(Track(track_id=10, name="Evil Walks", album_id=1))
session.add(Track(track_id=11, name="C.O.D.", album_id=1))
session.add(Track(track_id=12, name="Breaking The Rules", album_id=1))
session.add(Track(track_id=13, name="Night Of The Long Knives", album_id=1))
session.add(Track(track_id=14, name="Spellbound", album_id=1))
session.add(Track(track_id=15, name="Go Down" , album_id=4))
session.add(Track(track_id=16, name="Dog Eat Dog", album_id=4))
session.add(Track(track_id=17, name="Let There Be Rock", album_id=4))
session.add(Track(track_id=18, name="Bad Boy Boogie", album_id=4))
session.add(Track(track_id=19, name="Problem Child", album_id=4))
session.add(Track(track_id=20, name="Overdose", album_id=4))
session.add(Track(track_id=21, name="Hell Aint A Bad Place To Be",
album_id=4))
session.add(Track(track_id=22, name="Whole Lotta Rosie", album_id=4))
session.commit()
# Query set up
query = session.query(Album).filter(Album.album_id == 1)
track_1_alias = aliased(Track, name="Track1")
album_2_alias = aliased(Album, name="Album2")
track_2_alias = aliased(Track, name="Track2")
# Subqueries we want to load our relationships from
# This will match 9 tracks for AlbumId==1
track_1_subquery = session.query(track_1_alias).filter(
track_1_alias.track_id >= 4).subquery("Track1")
album_2_subquery = session.query(album_2_alias).subquery("Album2")
# This should only match 1 track for AlbumId==1
track_2_subquery = session.query(track_2_alias).filter(
track_2_alias.track_id < 4).subquery("Track2")
# Set up our joins
query = query.outerjoin(track_1_subquery, Album.tracks)
query = query.outerjoin(album_2_subquery, track_1_alias.album)
# Using album_2_alias.tracks for the second argument doesn't seem to work
here.
query = query.outerjoin(track_2_subquery,
album_2_alias.album_id == track_2_alias.album_id)
# Options - These don't work all at once, but seem fine separately
option1 = contains_eager("tracks", alias=track_1_subquery)
option2 = contains_eager("tracks.album", alias=album_2_subquery)
option3 = contains_eager("tracks.album.tracks", alias=track_2_subquery)
# Attempt to load just tracks
option3_query = query.options(option3)
results = option3_query.all()
assert(len(results) == 1)
# len here should be 9, but it's erroneously filtered to 1
assert(len(results[0].tracks) == 1)
assert(len(results[0].tracks[0].album.tracks) == 1)
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import create_engine
from sqlalchemy.orm import aliased, sessionmaker, relationship, contains_eager
from sqlalchemy import Column, ForeignKey, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
metadata = Base.metadata
class Album(Base):
__tablename__ = 'Album'
album_id = Column("AlbumId", Integer, primary_key=True)
title = Column("Title", Unicode(160), nullable=False)
class Track(Base):
__tablename__ = 'Track'
track_id = Column("TrackId", Integer, primary_key=True)
name = Column("Name", Unicode(200), nullable=False)
album_id = Column("AlbumId", ForeignKey('Album.AlbumId'), index=True)
album = relationship('Album', backref="tracks")
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine, checkfirst=True)
session_cls = sessionmaker(bind=engine)
session = session_cls()
# Set up the database with Chinook data
session.add(Album(album_id=1, title="For Those About To Rock We Salute You"))
session.add(Album(album_id=2, title="Balls to the Wall"))
session.add(Album(album_id=3, title="Restless and Wild"))
session.add(Album(album_id=4, title="Let There Be Rock"))
session.add(Track(track_id=1, name="For Those About To Rock", album_id=1))
session.add(Track(track_id=2, name="Balls to the Wall", album_id=2))
session.add(Track(track_id=3, name="Fast As a Shark", album_id=3))
session.add(Track(track_id=4, name="Restless and Wild", album_id=3))
session.add(Track(track_id=5, name="Princess of the Dawn", album_id=3))
session.add(Track(track_id=6, name="Put The Finger On You", album_id=1))
session.add(Track(track_id=7, name="Let's Get It Up", album_id=1))
session.add(Track(track_id=8, name="Inject The Venom", album_id=1))
session.add(Track(track_id=9, name="Snowballed", album_id=1))
session.add(Track(track_id=10, name="Evil Walks", album_id=1))
session.add(Track(track_id=11, name="C.O.D.", album_id=1))
session.add(Track(track_id=12, name="Breaking The Rules", album_id=1))
session.add(Track(track_id=13, name="Night Of The Long Knives", album_id=1))
session.add(Track(track_id=14, name="Spellbound", album_id=1))
session.add(Track(track_id=15, name="Go Down" , album_id=4))
session.add(Track(track_id=16, name="Dog Eat Dog", album_id=4))
session.add(Track(track_id=17, name="Let There Be Rock", album_id=4))
session.add(Track(track_id=18, name="Bad Boy Boogie", album_id=4))
session.add(Track(track_id=19, name="Problem Child", album_id=4))
session.add(Track(track_id=20, name="Overdose", album_id=4))
session.add(Track(track_id=21, name="Hell Aint A Bad Place To Be", album_id=4))
session.add(Track(track_id=22, name="Whole Lotta Rosie", album_id=4))
session.commit()
# Query set up
query = session.query(Album).filter(Album.album_id == 1)
track_1_alias = aliased(Track, name="Track1")
album_2_alias = aliased(Album, name="Album2")
track_2_alias = aliased(Track, name="Track2")
# Subqueries we want to load our relationships from
# This will match 9 tracks for AlbumId==1
track_1_subquery = session.query(track_1_alias).filter(
track_1_alias.track_id >= 4).subquery("Track1")
album_2_subquery = session.query(album_2_alias).subquery("Album2")
# This should only match 1 track for AlbumId==1
track_2_subquery = session.query(track_2_alias).filter(
track_2_alias.track_id < 4).subquery("Track2")
# Set up our joins
query = query.outerjoin(track_1_subquery, Album.tracks)
query = query.outerjoin(album_2_subquery, track_1_alias.album)
# Using album_2_alias.tracks for the second argument doesn't seem to work here.
query = query.outerjoin(track_2_subquery,
album_2_alias.album_id == track_2_alias.album_id)
# Options - These don't work all at once, but seem fine separately
option1 = contains_eager("tracks", alias=track_1_subquery)
option2 = contains_eager("tracks.album", alias=album_2_subquery)
option3 = contains_eager("tracks.album.tracks", alias=track_2_subquery)
# Attempt to load just tracks
option3_query = query.options(option3)
results = option3_query.all()
assert(len(results) == 1)
# len here should be 9, but it's erroneously filtered to 1
assert(len(results[0].tracks) == 1)
assert(len(results[0].tracks[0].album.tracks) == 1)
