0

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.

Paul
  • 25,812
  • 38
  • 124
  • 247

1 Answers1

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
Marlin Pierce
  • 9,931
  • 4
  • 30
  • 52
  • NoMethodError: undefined method `set_table_name' for Master:Class (Rails 3.2.3) – Paul May 18 '12 at 07:09
  • It seems that the select itself is able to accept an arbitrary SQL string. Thank you for the clue. A bookmark for myself: http://stackoverflow.com/questions/10374105/using-raw-sql-queries-in-rails-3-application – Paul May 18 '12 at 07:17
  • 1
    Should have included inheritance from AR. I'll edit my answer. – Marlin Pierce May 18 '12 at 13:26