On Thursday, July 2, 2015 at 4:22:42 PM UTC-7, Wesley Key wrote: > > Hello everyone! > > I'm trying to accomplish the below feat using #graph instead of raw SQL. > > *DB["SELECT games.id <http://games.id> AS game_id, games.title, > games.product_id, * > * games.barcode, games.release_date, * > * regions.alpha_3_code AS region_code, * > * publishers.name <http://publishers.name> AS publisher, > developers.name <http://developers.name> AS developer* > * FROM games* > * LEFT JOIN regions* > * ON (games.region_id = regions.id <http://regions.id>)* > * LEFT JOIN game_publishers* > * ON (games.id <http://games.id> = game_publishers.game_id)* > * LEFT JOIN publishers* > * ON (game_publishers.publisher_id = publishers.id > <http://publishers.id>)* > * LEFT JOIN game_developers* > * ON (games.id <http://games.id> = game_developers.game_id)* > * LEFT JOIN developers* > * ON (game_developers.developer_id = developers.id > <http://developers.id>)* > * "]* > > The problem is that when I start chaining, it starts using the previous > joined table name for the join conditions. Example: > > If I run this code: > > * DB[:games].select(* > * :id___game_id, * > * :title,* > * :product_id,* > * :barcode,* > * :release_date* > * ).graph(* > * :regions,* > * {* > * id: :region_id* > * },* > * {* > * join_type: :left,* > * select: [:alpha_2_code___region_code]* > * }* > * ).graph(* > * :game_publishers, * > * {* > * game_id: :id* > * }* > * ).graph(* > * :publishers,* > * {* > * id: :publisher_id* > * },* > * {* > * join_type: :left,* > * select: [:name___publisher]* > * }* > * )* > > This sequel produces (most cut out for brevity) : > > *LEFT JOIN "regions" ON ("regions"."id" = "games"."region_id") LEFT JOIN > "game_publishers" ON ("game_publishers"."game_id" = "regions"."id")* > > I assume I'm not doing this properly, or I'm missing how one sets a table > prefix on the column. (instead of "regions"."id" it should be "games"."id") > > Dataset#graph/#join will implicitly qualify unqualified values in the conditions hash with the last table joined. Because you want a condition that isn't qualified by the last table joined, you need to explicitly qualify the column: *.graph(* * :game_publishers, * * {* * game_id: :games__id* * }* * )*
Hopefully that will fix your issue. If you still have questions, please reply. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
