Bug in joinedload('*') ?!

[alex]

Hi all,

I want to use session.query(...).options(joinedload('*')) to load all
related objects eagerly.

It seems not to work if inheritance is involved.
This is a complete working example using SQLAlchemy 0.7.7 and Elixir
0.8.0dev:
---------------------
from elixir import *
from sqlalchemy import create_engine
from sqlalchemy.orm import joinedload

engine = create_engine('sqlite:///:memory:', echo = False)
metadata.bind = engine

class PersistentObject(Entity):
pass

class Movie(PersistentObject):
title = Field(Unicode(30))
director = ManyToOne('Director', inverse = "movies")

class Director(PersistentObject):
name = Field(Unicode(60))
movies = OneToMany('Movie', inverse = "director")

setup_all(True)

rscott = Director(name=u"Ridley Scott")
alien = Movie(title=u"Alien")
brunner = Movie(title=u"Blade Runner")
rscott.movies.append(brunner)
rscott.movies.append(alien)
session.commit()

print "without joinedload"
session.close_all()
d = session.query(Director).first()
for i in session: print i
print "with joinedload"
session.close_all()
d = session.query(Director).options(joinedload('*')).first()
for i in session: print i

------------------

The last line should also print the movies, which does not happen.
When you set Entity as the baseclass of Movie and Director it works.
Is this a bug or is there a reason I don't see?

Thx in advance,
alex

[Michael Bayer]

When you eager load from a class to itself, this is a self-referential eager load. A generic lazy="joined" needs to know the depth at which you'd like to go when this occurs, otherwise it will stop as soon as it sees itself in the path of classes to be joined. The behavior of joinedload('*') is the same as setting lazy="joined" on the relationship - it's not the same as saying joinedload(Director.movies), which it would take to mean as an explicit, single joined load.

In this case, even though this is from a base class to itself, the subclass is different, so perhaps the check for self-referential joining needs to be tuned. that is ticket #2481. Here, if you must use joinedload('*') and not regular joinedload(Director.movies) (I'd never use '*' with joinedload, add a new relationship and your app grinds to a halt), then add a join depth to the relationship (sorry, this is relationship(), I don't know how to set this with Elixir):

movies = relationship("Movie", foreign_keys=Movie.director_id, join_depth=2)

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[alex]

Hi,

thank you so much for the quick reply. That's exactly what I need!

FYI: I use  '*' with joinedload for the following scenario:

I want to export a certain object and all its related objects to a second sqlite database. In addition to the db file I also copy files from disk, which are referenced by certain db objects.

The sql and data files are packed into a zip to be transfered to another client, that imports it.

To do so, I create a new session and query the root object with joinedload('*') to have all related objects in the session, so I can do this:

objs = []

# store all session objects

for o in session:

    objs.append(o)

# expunge objects from session

for o in objs:

    orm.session.make_transient(o)

# add objects to session connected to second db with same metadata

exportSession.add_all(objs)

exportSession.commit()

exportSession.close_all()

best regards and a big praise for your work on sqlalchemy :D

alex