I am trying to impliment search functionality for user records with first_name, last_name, email, project_name,feature_name. Here first_name, last_name and email is from one table(User), project_name from table Project and feature_name from table Feature.Association of models are given below.I have a user index page where lists all users from table User. Need a search which search for users which we are entering.

model user.rb:

class User < ApplicationRecord
  has_many :project_users, dependent: :destroy
  has_many :projects, through: :project_users, dependent: :destroy
end

User have fields of first_name, last_name, email etc(using these three fields for search)

model project.rb

class Project < ApplicationRecord
  belongs_to :user
  has_many :project_users, dependent: :destroy
  has_many :features, dependent: :destroy
  has_many :users, through: :project_users, source: :user
end

Project have project_name(we search using project name)

model feature.rb

class Feature < ApplicationRecord
  belongs_to :project
end

Feature have feature_name(with feature_name we need to search)

What I am look for

We have params[:search_member] which contains the searched item(first_name, last_name, email, project_name, feature_name

For ex:

params[:search_member] = "John"
params[:search_member] = "Project1"
params[:search_member] = "Feature1"
params[:search_member] = "[email protected]"

Need a single query which checks the "params[:search_member]" in these three tables(User, Project and Feature) in fields first_name, last_name, email, project_name, and feature_name and return the users of searched value.

Working of associations

current_user.projects # will return all projects belongs to current user

project.users # return all users belongs to project

feature.project # return project that feature belongs to

and

feature.project.users # will return all users of projects

def search_all
  if params[:search_member].present?
     #need query here
  else
    User.all
  end
end

1. If I enter project_name it will return all users of that particular project

2. If I enter first_name, last_name or email return all users of this details

3. If I enter feature name, return all users of project that the feature belongs to

Trying to do in a single joins query

CodePudding user response：

I would try this

def search_all
  if params[:search_member].present?
    User.includes(projects: :features)
        .where(first_name: params[:search_member])
        .or(User.where(last_name: params[:search_member])
        .or(User.where(email: params[:search_member])
        .or(Project.where(project_name: params[:search_member]))
        .or(Feature.where(feature_name: params[:search_member]))
  else
    User.all
  end
end

and if that works as expected, then I would refactor the different subqueries to scopes in the models to make is easier to read.

CodePudding user response：

Solution

if params[:search_member].present
  search = params[:search_member]
  User.joins(projects: :features).where('users.first_name ILIKE :search
        OR users.last_name ILIKE :search
        OR users.email ILIKE :search
        OR projects.project_name ILIKE :search
        OR features.name ILIKE :search', search: "%#{search}%").references(:projects).uniq
else
  User.all
end