After looking through several options including pg_search, I'm having difficulty locating a resource that can help me understand how to put together multi-table, multi-filter searches. I have two models, profile and subject, both of which are related through a unique_id. What I'd like to do, is have the user select from numerous filters, across many models, and return the results. For example, I'd like the user to be able to search the name attribute from the profile model and the grade attribute from the subject model and return a list that falls within those search parameters. I have no problems creating one search filter, but when I add more filters for different categories, the other search filters don't seem to be communicating with one another. Below is some of my code. Happy to add more code, just really don't know where to being with this. Any help or direction you could provide would be greatly appreciated.
Profile DB
class CreateProfiles < ActiveRecord::Migration
def change
create_table :profiles do |t|
t.integer :unique_id
t.string :name
t.integer :age
t.string :sex
t.references :user, index: true, foreign_key: true
t.timestamps null: false
end
end
end
Subject DB
class CreateSubjects < ActiveRecord::Migration
def change
create_table :subjects do |t|
t.integer :unique_id
t.string :subject
t.integer :grade
t.references :user, index: true, foreign_key: true
t.timestamps null: false
end
end
end
Profile Model
class Profile < ActiveRecord::Base
include PgSearch
belongs_to :user
def self.import(file)
CSV.foreach(file.path, headers: true) do |row|
attributes = row.to_hash
Profile.create! attributes
end
end
end
Subject Model
class Subject < ActiveRecord::Base
include PgSearch
belongs_to :user
def self.import(file)
CSV.foreach(file.path, headers: true) do |row|
attributes = row.to_hash
Subject.create! attributes
end
end
end
Take a look at Rails join table queries and Rails method chaining to start.
Let's look at some examples of what you might do. Let say you want to find users who have a profile name of "John" and a subject with the grade of 100. You can do the following:
@users = User.where(profiles: { name: "John" }, subjects: { grade: 100 }).joins(:profiles, :subjects)
Note that using the hash method works only if you filter based on specific values. Let say now you want to find users who have a profile name that beings with "John" (such as "John", "John Smith", or "John Doe") and a grade greater than 85, you would do the following:
@users = User.where("profiles.name ILIKE ? AND subjects.grade > ?", "John%", 85).joins(:profiles, :subjects)
The ILIKE query works with Postgres, not MySQL as far I remember. Note the fact that in both statements you have to mention the joining table name in the query and you have to call the joins method as well.
So now that you know how to join tables, we can now look at how to use the params and scopes to filter it.
class User
scope :by_profile_name, -> (input) do
where("profiles.name = ?", input[:profile_name]).joins(:profiles) if input[:profile_name]
end
scope :by_subject_grade, -> (input) do
where("subjects.grade = ?", input[:subject_grade].to_i).joins(:subjects) if input[:subject_grade]
end
end
Then in your controller you would have:
@users = User.by_subject_grade(params).by_profile_name(params)
This is just a rough start take a look at the links above for more details.