CODEWITHSUNDEEP
rubyactiverecord
Bala
Bala

Reputation: 11274

How do I implement this in ActiveRecord

I am using this query directly and want to use ActiveRecord,

SELECT count(*)
  FROM p
       LEFT JOIN
          (SELECT pid
             FROM s LEFT JOIN i ON s.key = i.sid
            WHERE i.default = 'y') AS table_x
       ON p.pid = table_x.pid WHERE isnull(table_x.pid) AND p.show = 'y'

But I am not quite sure how to implement the above. The definition I have so far is 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 I < ActiveRecord::Base
  belongs_to :s, :foreign_key => 'sid'
  belongs_to :p, :through => :s
end

The part I am keen to know is on how to create/bring the subselect as a table/model?

Upvotes: 0

Views: 83

Answers (1)

Alfred Fazio
Alfred Fazio

Reputation: 956

One issue here is that you are trying to perform a join on tables based on column (pid) which you are asking to be null. You cannot join on NULL values. However, if that was a mistake and you do not want to join on NULL pid values, then an equivalent SQL statement would be as follows (assuming that the s table contains pid, not i):

SELECT count(*) FROM p 
LEFT JOIN s ON s.pid=p.pid 
LEFT JOIN i ON s.key=i.sid 
WHERE i.default='y' AND p.show = 'y'

This query very easily translates to ActiveRecord as you can then simply use the .joins() method concatenated by a .where() method. Maybe something like this could work for you:

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

Upvotes: 1

Related Questions