Hi, I understand the problem. Do you think I can solve this in my application code without changing the db structure an without getting temp table?
It's a quite large application and this is becoming a quite big problem when we are fetching bloggers based on info in the blog table. Would be great if you have any tips! Cheers, Patrick 23 maj 2012 kl. 18:29 skrev Jeremy Evans <[email protected]>: > 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. -- You received this message because you are subscribed to the Google Groups "sequel-talk" 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/sequel-talk?hl=en.
