Re: [sqlalchemy] contains_eager, outerjoin, subquery, alias, and loading nested relationships of the same type
Hm, I'll spend some time playing around with enable_relationship_loading
and see if I can produce what I'm looking for. Thanks again for the help!
On Tuesday, August 7, 2018 at 12:47:52 PM UTC-5, Mike Bayer wrote:
>
> On Mon, Aug 6, 2018 at 8:01 PM, Nick Repole > wrote:
> > Appreciate the response, that all makes sense to me. In hindsight, I'm
> > wondering how I ever expected there to be two Album objects with the
> same
> > identifier and different relationship collections...
> >
> > On a conceptual level, I wonder how feasible it'd be to have objects
> > expunged immediately as they're loaded. That way the parent Album with
> > album_id==1 could load, have it's tracks load, then when the child album
> is
> > loaded it wouldn't be the same Album object despite being album_id==1.
> >
> > My use case is purely read only, so I'm not particularly concerned with
> any
> > changes being tracked/persisted,so such a scenario would accomplish what
> I'd
> > need. But I realize my use case is pretty niche.
>
> well it would be that the objects aren't put into the session at all
> as they are loaded, because you literally want the eager loading
> within the same load to create a new Album object. That wouldn't
> work because the loading mechanism is continuously seeing the same
> primary key at the top for that Album and pulling it from the identity
> map per-row in order to implement the eager loading in the first
> place.
>
> if you want to rely upon lazy loading, that is possible.The
> session supports lazy-loading on non-session-bound objects:
>
> http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.enable_relationship_loading
>
> .
>
>
>
>
> >
> >
> >
> > On Monday, August 6, 2018 at 9:58:33 AM UTC-5, Mike Bayer wrote:
> >>
> >> On Sat, Aug 4, 2018 at 11:27 PM, Nick Repole
> wrote:
> >> > 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:
> >>
> >> great test case. What you're doing is not possible because you are
> >> looking at the "album.tracks" collection in two different contexts,
> >> and expecting them to have different contents, but this is the same
> >> Album object and the same "tracks" collection:
> >>
> >> (Pdb) results[0]
> >> <__main__.Album object at 0x7f2e1eea3898>
> >> (Pdb) results[0].tracks[0].album
> >> <__main__.Album object at 0x7f2e1eea3898>
> >> (Pdb) results[0].tracks is results[0].tracks[5].album.tracks
> >> True
> >>
> >> this is how the ORM identity map works, for any class / primary key,
> >> there's only one of them.
> >>
> >> now since this is SQLAlchemy, there are ways to do this, but it means
> >> you'd have two different Album objects with the same primary key in
> >> play. if you were to change both of them in a session and try to
> >> flush, you will get conflicts and/or errors.
> >>
> >> Here's the quickest way, note thre's now Album.tracks.parent_album for
> >> the real relationship:
> >>
> >> class Album(Base):
> >>
> >> __tablename__ = 'Album'
> >>
> >> album_id = Column("AlbumId", Integer, primary_key=True)
> >> title = Column("Title", Unicode(160), nullable=False)
> >> tracks = relationship("Track", backref="parent_album")
> >>
> >>
> >> class SubAlbum(Album):
> >> pass
> >>
> >>
> >> 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('SubAlbum')
> >>
> >>
> >> query = session.query(Album).filter(Album.album_id == 1)
> >> track_1_alias = aliased(Track, name="Track1")
> >> album_2_alias = aliased(SubAlbum, name="Album2")
> >> track_2_alias = aliased(Track, name="Track2")
> >>
> >> then your assertion passes.
> >>
> >>
> >> as you can see it is not possible for album<->track to be a
> >> bidirectional relationship and refer to two different Album objects at
> >> the same time. you have to choose one or the other.
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> >
> >> > from
Re: [sqlalchemy] contains_eager, outerjoin, subquery, alias, and loading nested relationships of the same type
On Mon, Aug 6, 2018 at 8:01 PM, Nick Repole wrote:
> Appreciate the response, that all makes sense to me. In hindsight, I'm
> wondering how I ever expected there to be two Album objects with the same
> identifier and different relationship collections...
>
> On a conceptual level, I wonder how feasible it'd be to have objects
> expunged immediately as they're loaded. That way the parent Album with
> album_id==1 could load, have it's tracks load, then when the child album is
> loaded it wouldn't be the same Album object despite being album_id==1.
>
> My use case is purely read only, so I'm not particularly concerned with any
> changes being tracked/persisted,so such a scenario would accomplish what I'd
> need. But I realize my use case is pretty niche.
well it would be that the objects aren't put into the session at all
as they are loaded, because you literally want the eager loading
within the same load to create a new Album object. That wouldn't
work because the loading mechanism is continuously seeing the same
primary key at the top for that Album and pulling it from the identity
map per-row in order to implement the eager loading in the first
place.
if you want to rely upon lazy loading, that is possible.The
session supports lazy-loading on non-session-bound objects:
http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.enable_relationship_loading
.
>
>
>
> On Monday, August 6, 2018 at 9:58:33 AM UTC-5, Mike Bayer wrote:
>>
>> On Sat, Aug 4, 2018 at 11:27 PM, Nick Repole wrote:
>> > 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:
>>
>> great test case. What you're doing is not possible because you are
>> looking at the "album.tracks" collection in two different contexts,
>> and expecting them to have different contents, but this is the same
>> Album object and the same "tracks" collection:
>>
>> (Pdb) results[0]
>> <__main__.Album object at 0x7f2e1eea3898>
>> (Pdb) results[0].tracks[0].album
>> <__main__.Album object at 0x7f2e1eea3898>
>> (Pdb) results[0].tracks is results[0].tracks[5].album.tracks
>> True
>>
>> this is how the ORM identity map works, for any class / primary key,
>> there's only one of them.
>>
>> now since this is SQLAlchemy, there are ways to do this, but it means
>> you'd have two different Album objects with the same primary key in
>> play. if you were to change both of them in a session and try to
>> flush, you will get conflicts and/or errors.
>>
>> Here's the quickest way, note thre's now Album.tracks.parent_album for
>> the real relationship:
>>
>> class Album(Base):
>>
>> __tablename__ = 'Album'
>>
>> album_id = Column("AlbumId", Integer, primary_key=True)
>> title = Column("Title", Unicode(160), nullable=False)
>> tracks = relationship("Track", backref="parent_album")
>>
>>
>> class SubAlbum(Album):
>> pass
>>
>>
>> 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('SubAlbum')
>>
>>
>> query = session.query(Album).filter(Album.album_id == 1)
>> track_1_alias = aliased(Track, name="Track1")
>> album_2_alias = aliased(SubAlbum, name="Album2")
>> track_2_alias = aliased(Track, name="Track2")
>>
>> then your assertion passes.
>>
>>
>> as you can see it is not possible for album<->track to be a
>> bidirectional relationship and refer to two different Album objects at
>> the same time. you have to choose one or the other.
>>
>>
>>
>>
>>
>>
>>
>>
>> >
>> > 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 =
Re: [sqlalchemy] contains_eager, outerjoin, subquery, alias, and loading nested relationships of the same type
Appreciate the response, that all makes sense to me. In hindsight, I'm
wondering how I ever expected there to be two Album objects with the same
identifier and different relationship collections...
On a conceptual level, I wonder how feasible it'd be to have objects
expunged immediately as they're loaded. That way the parent Album with
album_id==1 could load, have it's tracks load, then when the child album is
loaded it wouldn't be the same Album object despite being album_id==1.
My use case is purely read only, so I'm not particularly concerned with any
changes being tracked/persisted,so such a scenario would accomplish what
I'd need. But I realize my use case is pretty niche.
On Monday, August 6, 2018 at 9:58:33 AM UTC-5, Mike Bayer wrote:
>
> On Sat, Aug 4, 2018 at 11:27 PM, Nick Repole > wrote:
> > 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:
>
> great test case. What you're doing is not possible because you are
> looking at the "album.tracks" collection in two different contexts,
> and expecting them to have different contents, but this is the same
> Album object and the same "tracks" collection:
>
> (Pdb) results[0]
> <__main__.Album object at 0x7f2e1eea3898>
> (Pdb) results[0].tracks[0].album
> <__main__.Album object at 0x7f2e1eea3898>
> (Pdb) results[0].tracks is results[0].tracks[5].album.tracks
> True
>
> this is how the ORM identity map works, for any class / primary key,
> there's only one of them.
>
> now since this is SQLAlchemy, there are ways to do this, but it means
> you'd have two different Album objects with the same primary key in
> play. if you were to change both of them in a session and try to
> flush, you will get conflicts and/or errors.
>
> Here's the quickest way, note thre's now Album.tracks.parent_album for
> the real relationship:
>
> class Album(Base):
>
> __tablename__ = 'Album'
>
> album_id = Column("AlbumId", Integer, primary_key=True)
> title = Column("Title", Unicode(160), nullable=False)
> tracks = relationship("Track", backref="parent_album")
>
>
> class SubAlbum(Album):
> pass
>
>
> 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('SubAlbum')
>
>
> query = session.query(Album).filter(Album.album_id == 1)
> track_1_alias = aliased(Track, name="Track1")
> album_2_alias = aliased(SubAlbum, name="Album2")
> track_2_alias = aliased(Track, name="Track2")
>
> then your assertion passes.
>
>
> as you can see it is not possible for album<->track to be a
> bidirectional relationship and refer to two different Album objects at
> the same time. you have to choose one or the other.
>
>
>
>
>
>
>
>
> >
> > 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
Re: [sqlalchemy] contains_eager, outerjoin, subquery, alias, and loading nested relationships of the same type
On Sat, Aug 4, 2018 at 11:27 PM, Nick Repole wrote:
> 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:
great test case. What you're doing is not possible because you are
looking at the "album.tracks" collection in two different contexts,
and expecting them to have different contents, but this is the same
Album object and the same "tracks" collection:
(Pdb) results[0]
<__main__.Album object at 0x7f2e1eea3898>
(Pdb) results[0].tracks[0].album
<__main__.Album object at 0x7f2e1eea3898>
(Pdb) results[0].tracks is results[0].tracks[5].album.tracks
True
this is how the ORM identity map works, for any class / primary key,
there's only one of them.
now since this is SQLAlchemy, there are ways to do this, but it means
you'd have two different Album objects with the same primary key in
play. if you were to change both of them in a session and try to
flush, you will get conflicts and/or errors.
Here's the quickest way, note thre's now Album.tracks.parent_album for
the real relationship:
class Album(Base):
__tablename__ = 'Album'
album_id = Column("AlbumId", Integer, primary_key=True)
title = Column("Title", Unicode(160), nullable=False)
tracks = relationship("Track", backref="parent_album")
class SubAlbum(Album):
pass
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('SubAlbum')
query = session.query(Album).filter(Album.album_id == 1)
track_1_alias = aliased(Track, name="Track1")
album_2_alias = aliased(SubAlbum, name="Album2")
track_2_alias = aliased(Track, name="Track2")
then your assertion passes.
as you can see it is not possible for album<->track to be a
bidirectional relationship and refer to two different Album objects at
the same time. you have to choose one or the other.
>
> 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",
