Query a collection based on an association, and then return the whole association

Question

It's hard to squeeze this whole question into a short title. I have a Records model, and each record has many :tags, and has many :words, through: :tags. Also, the Word table has a string column, holding the string form of the word.

I'm trying to build a search query, so that users can search for records with certain words and see all the words that each returned record has. However, so far when I have the queried Records only the words that I queried on are included. I need to show all the words (even the un-searched words) for each record. But I can't figure this out and it's been a week or two of reading SO questions and Rails docs. I've tried using plain rails ActiveRecord stuff, and I tried using Arel tables. I have one working solution, but that involves building an array of the plucked ids of all the found Records, and finding them again, which just puts a bad taste in my mouth. Anyway, here's what I have:

record.rb

class Record < ActiveRecord::Base
  has_many :tags, dependent: :destroy
  has_many :words, through: :tags

  # this is the kind of query that I want
  # +search+ is an array of words, ex: %w{chick fil a}
  # however, `scope.first.words.count != scope.first.words(true).count`
  # that is, the first record's words are not all force reloaded automatically
  def self.that_have_all_words_in_rails(search)
    scope = includes(:words)
    search.each do |search_word|
      scope = scope.where(words: { string: search_word })
    end
    scope
  end

  # I also tried this in arel, but it seems to give the same result as above
  def self.that_have_all_words_in_arel(search)
    scope = joins(:words)
    search.each do |search_word|
      scope = scope.where(word_table[:string].eq(search_word))
    end
    scope.includes(:words)
  end

  def word_table
    Record.arel_table
  end

  # This is the only working version that I have, but it seems
  # sloppy to use the record_ids array like this.
  # That could be a 1000+ element array!
  def self.that_have_all_words_in(search)
    records = includes(:words)
    record_ids = search.inject(pluck(:id)) do |ids, search_word|
      ids & records.where(words: { string: search_word }).pluck(:id)
    end
    where(id: record_ids)
  end

word.rb

class Word < ActiveRecord::Base
  belongs_to :category
  has_many :tags, dependent: :destroy
  has_many :records, through: :tags
end

So, any ideas on how to be able to perform a query like:

Record.that_have_all_words_in(['chick', 'fil', 'a']).first.words

so that I get all the words of the first record, including words that are not 'chick' or 'fil' or 'a', without having to force reload with first.words(true)?

Thanks.

Update: the relevant parts of my schema

ActiveRecord::Schema.define(version: 20150727041434) do
  create_table "records", force: true do |t|
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "tags", force: true do |t|
    t.integer  "word_id"
    t.integer  "record_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "words", force: true do |t|
    t.string   "string"
    t.datetime "created_at"
    t.datetime "updated_at"
  end
end

Also, I'm using sqlite3 for my db.

Answer 1

You could write a custom join with aliased tables to match the search conditions:

def self.where_tagged_words_match_strings(search)
  search.uniq!
  joins("INNER JOIN tags mtags ON mtags.record_id = records.id INNER JOIN words mwords ON mwords.id = mtags.word_id")
    .where("mwords.string IN (?)", search).group("records.id").having("COUNT(DISTINCT mwords.string) = #{search.length}")
end

ETA This query should select records with words matching an arbitrary search array. It does this by selecting records whose words match any of the strings in the array, then grouping by records' id, selecting only those having a number of matching strings equal to the number of strings queried.

You can then chain this with includes(:words) to get all of the words associated, since the query above uses the aliased mwords:

Record.where_tagged_words_match_strings(search).includes(:words)

Relatedly, while all of the above should work in SQLite, I highly recommend that you switch to a more powerful and production-ready SQL database such as MySQL or PostgreSQL.