How to write multiple COUNT(*) subqueries in Rails?

Question

How to write subqueries in Rails like this?:

select
    m.field1,
    m.field2,
    (select count(*) from details1 d1 where d1.id=m.field3) as count1,
    (select count(*) from details2 d2 where d2.id=m.field4) as count2,
    (select count(*) from details3 d3 where d3.id=m.field5) as count3
from
    master m

Database server is PostgreSQL.

Marlin Pierce · Accepted Answer · 2012-05-18 13:26:30Z

3

class Master < ActiveRecord::Base
  set_table_name "master"
end
masters = Master.select(%Q"master.field1, master.field2,
(select count(*) from details1 d1 where d1.id=m.field3) as count1,
(select count(*) from details2 d2 where d2.id=m.field4) as count2,
(select count(*) from details3 d3 where d3.id=m.field5) as count3").all

edited May 18, 2012 at 13:26

answered May 17, 2012 at 19:16

Marlin Pierce

10.1k5 gold badges33 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Paul Over a year ago

NoMethodError: undefined method `set_table_name' for Master:Class (Rails 3.2.3)

Paul Over a year ago

It seems that the select itself is able to accept an arbitrary SQL string. Thank you for the clue. A bookmark for myself: stackoverflow.com/questions/10374105/…

Marlin Pierce Over a year ago

Should have included inheritance from AR. I'll edit my answer.

Collectives™ on Stack Overflow

How to write multiple COUNT(*) subqueries in Rails?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related