CODEWITHSUNDEEP
rubyruby-on-rails-3joinsearch
Ronats
Ronats

Reputation: 329

Search users based on multiple elements

I am trying to implement 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 shown 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

Upvotes: 1

Views: 64

Answers (3)

Ekzosta
Ekzosta

Reputation: 86

I suggest using pg_search gem (better quality, cleaner code) and then it can be like that:

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

  pg_search_scope :search_member,
    against: [:first_name, :last_name, :email],
    associated_against: {
      projects: :name,
      features: :name
    }
end

And then:

def search_all
  if params[:search_member].present?
     User.search_member(params[:search_member])
  else
    User.all
  end
end

Upvotes: 1

Ronats
Ronats

Reputation: 329

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

Upvotes: 0

spickermann
spickermann

Reputation: 107142

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.

Upvotes: 1

Related Questions