On Wednesday, May 23, 2012 8:53:46 AM UTC-7, PatrickLef wrote: > > Hi, > > I'm having problems when I combine Class table inheritance with eager > graph. Which creates a subselect with all the "bloggers" (see gist) > where it joins in the user table. Which makes this query really slow. > > Here's a code example: > https://gist.github.com/cd45201eb1e856590243 > > Here's the generated query: > SELECT `users`.`user_id`, `users`.`blogger_custom_field`, > `blog`.`blog_id`, `blog`.`user_id` AS `blog_user_id`, > `blog`.`blog_custom_field` > FROM ( > SELECT * FROM `users` > INNER JOIN `bloggers` USING (`user_id`) > ) AS `users` > LEFT OUTER JOIN `blogs` AS `blog` ON (`blog`.`user_id` = > `users`.`user_id`) > > Here's how I would like it: > SELECT `users`.`user_id`, `bloggers`.`blogger_custom_field`, > `blog`.`blog_id`, `blog`.`user_id` AS `blog_user_id`, > `blog`.`blog_custom_field` > FROM users ( > INNER JOIN `bloggers` USING (`user_id`) > LEFT OUTER JOIN `blogs` AS `blog` ON (`blog`.`user_id` = > `users`.`user_id`) > > Is there something that I can change to make it work this way or is > this a bug? >
Does it give the wrong result? If not, then no, it's not a bug. The reason for the subselect is that your Blogger class dataset uses a join (since it is a subclass of User, which uses CTI). Whenever you attempt to initiate a graph on a dataset that uses a join, the initial dataset is wrapped in a subselect (https://github.com/jeremyevans/sequel/blob/master/lib/sequel/dataset/graph.rb#L105). This is because of the necessity of qualifying the columns correctly. Graphing needs to qualify all of the columns in the initial dataset. However, Sequel does not know which table in the dataset which columns come from. In your case, Sequel just knows that Blogger.dataset has columns :user_id and :blogger_custom_field, but it does not know that user_id comes from users and blogger_custom_field comes from bloggers. The only safe way for Sequel to operate is to wrap the initial dataset in a subselect, and qualify all columns with the subselect's alias. FYI, most decent databases would optimize both of those queries the same way. For example, on PostgreSQL, you get the same exact query plan for both queries. Conclusion: Use a database that sucks less :) Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To view this discussion on the web visit https://groups.google.com/d/msg/sequel-talk/-/cikOkyl_8zgJ. 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/sequel-talk?hl=en.
