has_many doesn't fetch all when includes(:association).where("association.something = ?", value)

Question

I need your help:

I have Three Models:

Phonogram has_many PhonogramInstrument:

class Phonogram < ActiveRecord::Base
  #... things
  has_many :phonogram_instruments, source: :filter, dependent: :destroy, inverse_of: :phonogram
  has_many :instruments, source: :filter, through: :phonogram_instruments
end

PhonogramInstrument belongs_to Filter:

class PhonogramInstrument < ActiveRecord::Base
  belongs_to :phonogram
  belongs_to :filter
end

and Filter:

class Filter < ActiveRecord::Base

end

So, I need to fetch all phonograms that have filter_id 30 for example:

@phonograms = @phonograms.includes(:phonogram_instruments).where("phonogram_instruments.filter_id = ?", 30)

That fetches me one list with one object (exactly what I was expected):

=> [#<Phonogram id: 14, title: "Test", version_title: "", notes: nil, isrc: "BR3BD1300003", release_date: "2013-02-01", created_at: "2014-02-23 16:54:24", updated_at: "2014-08-27 03:19:08">]

...but if I take this object and show its instrument:

    @phonograms.first.phonogram_instruments.inspect
=> #<ActiveRecord::Associations::CollectionProxy [#<PhonogramInstrument id: 51, phonogram_id: 14, filter_id: 30, created_at: "2014-08-27 07:01:27", updated_at: "2014-08-27 07:01:27">]>

but at this phonogram I have 7 PhonogramInstruments:

 Phonogram.find(14).phonogram_instruments.inspect
=> "#<ActiveRecord::Associations::CollectionProxy [
#<PhonogramInstrument id: 25, phonogram_id: 14, filter_id: 25, created_at: \"2014-06-04 06:11:14\", updated_at: \"2014-06-04 06:11:14\">, 
#<PhonogramInstrument id: 26, phonogram_id: 14, filter_id: 26, created_at: \"2014-06-04 06:11:14\", updated_at: \"2014-06-04 06:11:14\">, 
#<PhonogramInstrument id: 48, phonogram_id: 14, filter_id: 49, created_at: \"2014-08-27 03:19:07\", updated_at: \"2014-08-27 03:19:07\">, 
#<PhonogramInstrument id: 49, phonogram_id: 14, filter_id: 50, created_at: \"2014-08-27 03:19:08\", updated_at: \"2014-08-27 03:19:08\">, 
#<PhonogramInstrument id: 50, phonogram_id: 14, filter_id: 29, created_at: \"2014-08-27 07:01:27\", updated_at: \"2014-08-27 07:01:27\">,
#<PhonogramInstrument id: 51, phonogram_id: 14, filter_id: 30, created_at: \"2014-08-27 07:01:27\", updated_at: \"2014-08-27 07:01:27\">, 
#<PhonogramInstrument id: 52, phonogram_id: 14, filter_id: 31, created_at: \"2014-08-27 07:01:27\", updated_at: \"2014-08-27 07:01:27\">
]>"

Help please! :)

There is only one PhonogramInstrument matches the condition of phonogram_id 14 and filter_id 30, so ilrein's answer was right. — Jaugar Chang, Aug 28 '14 at 04:47
But the phonogram #14 has 7 PhonogramInstrument, and the where brings me ActiveRecord::Associations::CollectionProxy with my phonogram #14 with 1 PhonogramInstrument — Diego Polido Santana, Aug 28 '14 at 11:24
Can you execute your code `@phonograms.includes(:phonogram_instruments).where("phonogram_instruments.filter_id = ?", 30)` in rails controller? And run the SQL generated by rails on your database command window. You could post the SQL and the result on your question. — Jaugar Chang, Aug 28 '14 at 11:30

MaximusDominus · Answer 1 · 2014-08-29T05:53:08.490

4

Try joins instead of includes:

  @phonograms = Phonogram.joins(:phonogram_instruments).where("phonogram_instruments.filter_id = ?", 30)

see section on preload here

http://blog.arkency.com/2013/12/rails4-preloading/

edited Aug 29 '14 at 05:53

answered Aug 28 '14 at 18:58

MaximusDominus

2,017
18
11

I tried. I had an error because the previous @phonograms was including other associations – Diego Polido Santana Aug 29 '14 at 02:11

score 2 · Answer 2 · answered Aug 28 '14 at 03:33

2

You didn't explicitly ask a question. What is the problem? I see you are indicating that Phonogram # 14 has 7 objects associated to it, while Phonogram # 1 has 1 object associated to it (where the filter ID is set to 30).

Everything seems to be going as expected...

answered Aug 28 '14 at 03:33

ilrein

3,833
4
31
48

Both phonogram objects is the same #14 – Diego Polido Santana Aug 28 '14 at 11:22

score 0 · Answer 3 · answered Aug 29 '14 at 02:20

Done!

My error was because my previous list was including other association with includes():

@phonograms = @phonograms.includes([:artists, :partners, :labels, :albums]).where(...)

When I tried to use

@phonograms.joins("phonogram_instruments").where(...)

...I got an error near the word "phonogram_instruments" floating alone on my query.

So, I put manual LEFT OUTER JOIN on my query:

@phonograms = @phonograms.joins("LEFT OUTER JOIN \"#{listed_filter.type}\" \"#{listed_filter.type}_phonograms\" ON \"#{listed_filter.type}_phonograms\".\"phonogram_id\" = \"phonograms\".\"id\"").where("#{listed_filter.type}_phonograms.filter_id = ?", listed_filter.id)

It works! Thanks all!

has_many doesn't fetch all when includes(:association).where("association.something = ?", value)

3 Answers3