Rails 3 - where clause on has_many through association

Question

Hi there,

I have a little problem with a where query on a has_many :through association...

My setup is as follows:

PurchaseOrderAddressAssignment:

belongs_to :address

belongs_to :purchase_order

Address:

has_many :purchase_order_address_assignments

has_many :purchase_orders, :through => :purchase_order_address_assignments

PurchaseOrder:

has_many :purchase_order_address_assignments

has_many :addresses, :through => :purchase_order_address_assignments

My where clause:

PurchaseOrder.where("addresses.id = 168 and addresses.id = 169").includes(:addresses)

Is returning 0 Records... but there should be at least 1...

PurchaseOrder.where(:baan_id => "KD0005756").first.address_ids

Is returning [168, 169, 170, 327]

... I think I'm too stupid to solve this little problem :-/

Can someone tell me what I'm doing wrong here?

Thx,

Michael

Answer 1

I would probably do a custom finder method in this case.

class PurchaseOrder < ActiveRecordBase
  def self.with_addresses(*args)
    values = args.flatten.uniq

    # Note use :joins instead of :includes if you don't
    # want the addresses data
    includes(:addresses)
    where(:addresses => {:id => values})
    group("purchase_orders.id")
    having("count(ad‌​dresses.id)=#{values.size}")
  end
end

I'm pretty sure that should work.

Here's a similar answer that helps explain the query.

Answer 2

So you want the PurchaseOrder's where addresses are in some id's list

Try this:

PurchaseOrder.where(addresses: {id: [168, 169]}).includes(:addresses)