Alexey Kharchenko
Alexey Kharchenko

Reputation: 1032

Joining subqueries in Squeel

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

Answers (0)

Related Questions