ruby - How do I implement this in ActiveRecord -

i using query directly , want use activerecord,

select count(*)   p        left join           (select pid              s left join on s.key = i.sid             i.default = 'y') table_x        on p.pid = table_x.pid isnull(table_x.pid) , p.show = 'y' 

but not quite sure how implement above. definition have far below.

class p < activerecord::base   has_many :s, :foreign_key => 'pid'   has_many :i, :through => :s end   class s < activerecord::base   belongs_to :p, :foreign_key => 'pid'    has_many :i, :foreign_key => 'sid'  end  class < activerecord::base   belongs_to :s, :foreign_key => 'sid'   belongs_to :p, :through => :s end 

the part keen know on how create/bring subselect table/model?

one issue here trying perform join on tables based on column (pid) asking null. cannot join on null values. however, if mistake , not want join on null pid values, equivalent sql statement follows (assuming s table contains pid, not i):

select count(*) p  left join s on s.pid=p.pid  left join on s.key=i.sid  i.default='y' , p.show = 'y' 

this query translates activerecord can use .joins() method concatenated .where() method. maybe work you:

p.joins(:s => :i).where('i.default = ?', 'y').where('p.show = ?', 'y').count()