On Tuesday, June 12, 2012 2:29:30 PM UTC-4, Jeremy Evans wrote:
>
> On Tuesday, June 12, 2012 9:55:43 AM UTC-7, Sean Redmond wrote:
First, thanks for select_all
> The obvious follow up: can this be done just with associations? I have the
>> many_to_one set up in my TableA model so that it can, for instance,
>> retrieve all the associated records from TableB and TableC, but I'm not
>> sure how it would work just to filter TableA. The twist is that the fields
>> it needs to be filtered on exist in al the table which is why they have to
>> be spelled out (":table_c__field1 => 1") in the statement above.
>>
>>
> You'll have to provide more details/code before I can answer that.
>
I figured it out. I was looking for something like:
TableA.filter(:table_c_assoc => TableC.filter(:field1 => 1, :field2 =>
1)).filter(:table_b_assoc => TableB.filter(:field1 => 1))
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)?
--
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/-/76QZDSoRKQEJ.
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.
