multi-table, mult-filters postgresql search - rails

Question

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

Answer 1

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.