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 view this discussion on the web visit https://groups.google.com/d/msg/sequel-talk/-/zEkoUMCknfwJ. 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.
