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