Hi Jeremy, instead of posting a new thread, I thought I would dove-tail off this one. :)
Whenever you use join or association_join, do you have to include a select_all statement? If yes, this would be a difference from other ORMs like AR where they will by default target the main specific table. This is generally a good default, otherwise columns (like ID) end up conflicting. Maybe there is a default or something I'm missing, but basically whenever I do a join I have to also be thinking if I need the select_all. I'm guessing this was intended by design? Aryk On Tuesday, June 12, 2012 at 2:17:55 PM UTC-7, Jeremy Evans wrote: > > On Tuesday, June 12, 2012 12:12:09 PM UTC-7, Sean Redmond wrote: >> >> The generated SQL is wordy: >> >> SELECT * FROM `table_a` WHERE ((`table_a`.`a_id` IN (SELECT >> `table_b`.`a_id` FROM `table_b` WHERE ((`table_b`.`c_id` IN (SELECT >> `table_c`.`c_id` FROM `table_c` WHERE ((`field1` = 1) AND (`field2` = 1) >> AND (`table_c`.`c_id` IS NOT NULL)))) AND (`table_b`.`a_id` IS NOT NULL)))) >> AND (`table_a`.`a_id` IN (SELECT `table_b`.`a_id` FROM `table_b` WHERE >> ((`field` = 1) AND (`table_b`.`a_id` IS NOT NULL))))) >> >> at least compared to the join version (which is pretty much the SQL I'd >> have written by hand) >> >> SELECT DISTINCT `table_a`.* FROM `table_a` INNER JOIN `table_b` ON >> (`table_b`.`a_id` = `table_a`.`a_id`) INNER JOIN `table_c` ON >> (`table_c`.`c_id` = `table_b`.`c_id`) WHERE ((`table_b`.`field1` = 1) AND >> (`table_c`.`field1` = 1) AND (`table_c`.`field2` = 1)) >> >> The code using filters on the associations is much, much cleaner but how >> inefficient are those subselects going to turn out to be (this is using >> MySQL)? >> >> > Unfortunately, MySQL's planner/optimizer is known to handle subselects > poorly. Upgrade to a database that sucks less. :) When I did my testing > on PostgreSQL, using a subselect was faster than using a join for the cases > I tested. > > Unfortunately, the filter by associations code has to return a filter > expression, so it has to use a subselect. If you want to use a join, you > can use eager_graph. Maybe something like: > > TableA.eager_graph(:table_c_assoc, > :table_b_assoc).where(:table_b_assoc__field1=>1, :table_c_assoc__field1=>1, > :table_c_assoc__field2=>2) > > 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 https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
