Reputation: 1032
I'm trying to make some kind of pivotal table, joining one of the model tables (master) with it's many-to-many relations (rel1, rel2, ...), aggregating the number of related models. I'd expect it to produce SQL similar to the following:
SELECT
masters.id,
rel1.rel1_count,
rel2.rel2_count,
...
FROM
masters
LEFT JOIN
(SELECT
masters.id,
count(rel1s_masters.*) as rel1_count
FROM
masters
LEFT OUTER JOIN
rel1s_masters
ON
rel1s_masters.master_id = master.id
GROUP BY
masters.id) rel1
ON
rel1.id = masters.id
LEFT JOIN
(SELECT
...
...) rel2
ON
rel2.id = masters.id
...
I can easily create the internal queries with Squeel:
rel1_subq = Master \
.joins{ rel1s_masters.outer } \
.select{ masters.id.as id } \
.select{ rel1s_masters.count.as rel1_count } \
.group{ masters.id }
rel2_subq = Master \
.joins{ ... } \
.select{ ... } \
...
But now, I'm not sure how to combine these, well, column-subqueries into a summary table in a good way. The closest I got uses converting the subqueries into sql and handling them this way:
stats = Master \
.select{ masters.id } \
.joins{ ... } \
.joins{ ", (#{rel1_subq.to_sql}) as rel1" } \
.where{ rel1.id == masters.id } \
.select{ rel1.rel1_count.as rel1_count } \
.joins{ ", (#{rel2_subq.to_sql}) as rel2" } \
.where{ ... } \
.select{ ... }
Is there a nicer way to get the same?
UPD: Looks like in this case it's a good idea to go for Counter Cache approach:
http://railscasts.com/episodes/23-counter-cache-column
But the question is still valid: is there a smooth natural way to do the subquery joins with squeel?
Upvotes: 1
Views: 882