vendredi 7 août 2015

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

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.

Aucun commentaire:

Enregistrer un commentaire