Reputation: 11274
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
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