CODEWITHSUNDEEP
sqlrubyruby-on-rails-3activerecord
chetang
chetang

Reputation: 308

Rails activerecord inner join a custom object

I have classes:

class Want < ActiveRecord::Base
  has_many :cached_buy_offers, dependent: :destroy
end
class CachedBuyOffer < ActiveRecord::Base
  belongs_to :want
end

So, I can do 

Want.joins(:cached_buy_offers)

which works as expected. I want to generate following sql:

select * from wants
inner join 
(select cached_buy_offers.want_id, max(buy_offer_cents) as max_buy_offer,           count(cached_buy_offers.want_id) as buy_offer_count
from cached_buy_offers
where cached_buy_offers.want_id in (1,2,3,4)
group by cached_buy_offers.want_id
order by max_buy_offer) as cached_buy_offers
on cached_buy_offers.want_id = wants.id

Inner sql query can be generated using:

ids = [1,2,3,4]

CachedBuyOffer.select('cached_buy_offers.want_id, max(buy_offer_cents) as max_buy_offer, count(cached_buy_offers.want_id) as buy_offer_count').where('cached_buy_offers.want_id in (?)',ids).group('cached_buy_offers.want_id').order('max_buy_offer')

But when I try to do this:

Want.joins(CachedBuyOffer.select ..... the above activerecord inner query)

throws an error RuntimeError: unknown class: CachedBuyOffer

How can I generate the required sql?

Upvotes: 1

Views: 2245

Answers (2)

Sean Hill
Sean Hill

Reputation: 15056

You can use Arel.sql. 

ids = [1,2,3,4]

cached_buy_offer_subquery = CachedBuyOffer
.select('cached_buy_offers.want_id, 
  max(buy_offer_cents) as max_buy_offer, 
  count(cached_buy_offers.want_id) as buy_offer_count')
.where('cached_buy_offers.want_id in (?)',ids)
.group('cached_buy_offers.want_id')
.order('max_buy_offer').to_sql

Want.joins("INNER JOIN (#{Arel.sql(cached_buy_offer_subquery)}) cached_buy_offers ON cached_buy_offers.want_id = wants.id")

Upvotes: 2

The Brofessor
The Brofessor

Reputation: 2411

.joins takes an association key as an argument, such as Want.joins(:cached_buy_offer). You can chain queries off of that!

Upvotes: 0

Related Questions