To clarify, I would like the DAL syntax that produces a query along the following lines, where joins are using the same lookup table.
SELECT movie.title, star.name as star, dir.name as director FROM movie LEFT JOIN actor ON (movie.id = actor.movie_id) LEFT JOIN person as star ON (actor.person_id = star.id) LEFT JOIN director ON (movie.id = director.movie_id) LEFT JOIN person as dir ON (director.person_id = dir.id) ORDER BY movie.average_rating, movie.id LIMIT 1000 OFFSET 0; On Wednesday, August 29, 2012 6:10:46 PM UTC-4, Mike Girard wrote: > > Hello, > > Could someone tell me how I should modify the following to alias the > results for actor and director? Both actors and directors consist of a > movie_id and a person_id, linking them up respectively to a person.id and > a movie.id. > > db(db.movie.title == 'Citizen Kane').select(db.movie.title, *db**.person.name > <as star**>*, *db**.person.name <as director**>*, > left = [db.actor.on(db.movie.id == db.actor.movie_id), > db.person.on(db.actor.person_id == db.person.id), > db.director.on(db.movie.id == db.director.movie_id), > db.person.on(db.director.person_id == db.person.id)] > ) > > > > Not crufting this up with my model since I think this is a syntax issue. > My model is very standard many-to-many. > > > > > --
