0

Here's my situation. I have a set of lists of words. Some words can appear in more than one list. For each word/list combination, I might have an example sentence.

Words

class Word < ActiveRecord::Base
  has_and_belongs_to_many :lists
  has_many :sample_sentences
end

Lists

class List < ActiveRecord::Base
  has_and_belongs_to_many :words
  has_many :sample_sentences
end

Sample Sentences

class SampleSentence < ActiveRecord::Base
  belongs_to :word
  belongs_to :list
end

... and to support HABTM, I have a join table.

Everything works just fine except this:

Using eager loading, I would like to grab all words in a particular list and the sample sentences for that word for that particular list.

I have tried many variations with scopes, wheres, etc.

Most variations look something like this (testing in IRB)

w=Word.includes(:sample_sentences).where(Words: {id: 48}).where(sample_sentences: {list_id: 6})[0]

This works great if the word has an example sentence in the associated list. It does not work if the word has NO example sentence in the list. It returns no records.

The documentation Active Record Query Interface and countless questions on Stack Overflow tell me that the resulting sql uses a left outer join to get the data. I believe my issue is the result of the additional WHERE on the sample_sentences table which seems to eliminate the possibility of getting back a word from the query with no sample sentences.

This is indeed borne out.. if I drop the additional where on the sample_sentences table, I get records back regardless of whether or not the word has an associated sample sentence. The problem is, I also get back sample sentences in which I am not interested.

Development database engine is SQL Lite. Production will be MySQL.

So.. I'm not sure what the best thing to do would be. Thoughts?

Chris
  • 1,013
  • 1
  • 15
  • 35
  • If I am reading your request correctly `Using eager loading, I would like to grab all words in a particular list and the sample sentences for that word for that particular list.`. It appears that you want all words regardless of whether they have comments. Try this: `Word.includes(:sample_sentences, :lists).where(lists: {id: #List id })`. This will include the sample sentences if they exist and you can test with an `empty?`. – nikkon226 Nov 05 '14 at 15:37
  • @nikkon226 - this is great. Exactly what I needed. Thank you. Please post as answer and I will accept it. – Chris Nov 06 '14 at 02:44

1 Answers1

1

If I am reading your request correctly Using eager loading, I would like to grab all words in a particular list and the sample sentences for that word for that particular list.. It appears that you want all words regardless of whether they have comments. Try this: Word.includes(:sample_sentences, :lists).where(lists: {id: #List id }). This will include the sample sentences if they exist and you can test with an empty?.

nikkon226
  • 998
  • 6
  • 11