On Jan 20, 2012, at 6:59 PM, Justin Thiessen wrote: > Hi, > > I'm trying to use the 'contains_eager' option but experiencing some > difficulty figuring out how to make it generate what I need. > > Short background: > > The code is part of an automated search-generation algorithm that takes > kw=val constructs (such as user.name='bob', user.job.title='engineer', > user.job.desk.desk_make='steelcase') and joins related models as necessary to > produce the final query. > > (1) because the query is generated automatically, the code typically joins on > aliased models to avoid potential conflicts. > (2) there is no predetermined limit to the number of relationships the query > can span, and there are a number of cases where spanning 2 relationships is > very useful. > (3) it is necessary to be able to filter on any or all of the referenced > models > > I believe that (2) and (3) pretty much force me to explicitly handle the > joins myself and then use 'contains_eager' to eagerly load the related model > records, as described in: > > http://www.sqlalchemy.org/docs/orm/loading.html#contains-eager > > class User: > user_id = Integer > job = Relationship(Job) > (...) > > class Job: > job_id = Integer > desk=Relationship(Cubicle) > > class Cubicle: > cubicle_id = Integer > desk_make = Text > cubicle_location = Integer > > > q=Session.Query(User) > > job_alias=Aliased(Job) > cubicle_alias=Aliased(Cubicle) > > q=q.join(job_alias, from_jointpoint=True) > q=q.join(cube_alias, from_jointpoint=True) > > # now I wish to force eager loading. According to the sqlalchemy docs, > # I need to provide the complete path from User to Cubicle, > # which would lead me to use something like: > > q=q.options(contains_eager(User.job, Job.desk))
> # but also according to the docs, if I am using an alias, > # I need to supply that to the contains_eager option > # so that the aliased table can be properly identified. > > # the example in the docs looks like: > > q=q.options(contains_eager(User.job, alias=job_alias)) > > # if one of these approaches worked, then, of course, I could filter like so: > > q.filter(cubicle_alias.desk_make == 'steelcase') > > ---- > > Unfortunately I do not see how to provide the necessary full path from the > root of the query to the tip of the join and at the same time provide a value > for alias for each node of the join. > > I have taken a cursory look at the contains_eager function and followed the > trail of inherited methods/models back a couple of steps, but I do not > immediately see any way to induce it to handle both a list of joins and a > list of corresponding aliases. contains_eager() only cares about the columns it's going to be picking out of the result set. Here it seems you'd like the job_alias columns to populate into User.job, and the cubicle_alias columns into the Job.desk relationship. So two contains eagers, each illustrating the path plus target alias: contains_eager(User.job, alias=job_alias) contains_eager(User.job, Job.desk, alias=desk_alias) both are needed since the User->job->desk load can't take place unless Job objects are being loaded in the results. If that specific combination doesn't work, then I need a full test case which if it follows the pattern correctly, would mean it's a bug. I don't use contains_eager across more than one hop generally, though we should have test coverage for multiple paths.... -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
