Specific search implementation

Question

So I'm trying to improve the search feature for my app

My model relationships/associations are like so (many>one, one=one):

• Clients < Projects < Activities = Assignments = Users
• Assignments < Tasks

Tasks table has only a foreign key to assignments.

Search params look something like this:

params[:search]==User: 'user_handle', Client: 'client_name', Project: 'project_name', Activity: 'activity_name'

So I need to porbably search Clients.where().tasks, Projects.where().tasks and so on. Then I need to somehow concatenate those queries and get rid of all the duplicate results. How to do that in practice however, I have no clue.

I've been hitting my head against a brick wall with this and internet searches didn't really help... so any help is greatly apreciated. Its probably a simple solution too...

I am on rails 4.2.5 sqlite for dev pg for production

Answer 1

A few things I would change/recommend based on the code in your own answer:

1. Move the search queries into scopes on each model class
2. Prefer AREL over raw SQL when composing queries (here's a quick guide)
3. Enhance rails to use some sort of or when querying Models

The changes I suggest will enable you to do something like this:

search = search_params

tasks = Tasks.all
tasks = tasks.or.user_handle_matches(handle) if (handle = search[:user].presence)
tasks = tasks.or.client_name_matches(name) if (name = search[:client].presence)
tasks = tasks.or.project_name_matches(name) if (name = search[:project].presence)
tasks = tasks.or.activity_name_matches(name) if (name = search[:activity].presence)
@tasks = tasks.uniq

First, convert each of your queries to a scope on your models. This enables you to reuse your scopes later:

class User
  scope :handle_matches, ->(handle) {
    where(arel_table[:handle].matches("%#{handle}%"))
  }
end

class Client
  scope :name_matches, ->(name) {
    where(arel_table[:name].matches("%#{name}%"))
  }
end

class Project
  scope :name_matches, ->(name) {
    where(arel_table[:name].matches("%#{name}%"))
  }
end

class Activity
  scope :name_matches, ->(name) {
    where(arel_table[:name].matches("%#{name}%"))
  }
end

You can then use these scopes on your Task model to allow for better searching capabilities. For each of the scopes on Task we are doing an join (inner join) on a relationship and using the scope to limit the results of the join:

class Task
  belongs_to :assignment
  has_one :user, :through => :assignment
  has_one :activity, :through => :assignment
  has_one :project, :through => :activity

  scope :user_handle_matches, ->(handle) {
    joins(:user).merge( User.handle_matches(handle) )
  }

  scope :client_name_matches, ->(name) {
    joins(:client).merge( Client.name_matches(name) )
  }

  scope :activity_name_matches, ->(name) {
    joins(:activity).merge( Activity.name_matches(name) )
  }

  scope :project_name_matches, ->(name) {
    joins(:project).merge( Project.name_matches(name) )
  }
end

The final problem to solve is oring the results. Rails 4 and below don't really allow this out of the box but there are gems and code out there to allow this functionality.

I often include the code in this GitHub gist in an initializer to allow oring of scopes. The code allows you to do things like Person.where(name: 'John').or.where(name: 'Jane').

Many other options are discussed in this SO question.

If you don't want include random code and gems, another option is to pass an array of ids into the where clause. This generates a query similar to SELECT * FROM tasks WHERE id IN (1, 4, 5, ...):

tasks = []
tasks << Tasks.user_handle_matches(handle) if (handle = search[:user].presence)
tasks << tasks.or.client_name_matches(name) if (name = search[:client].presence)
tasks << tasks.or.project_name_matches(name) if (name = search[:project].presence)
tasks << tasks.or.activity_name_matches(name) if (name = search[:activity].presence)

# get the matching id's for each query defined above
# this is the catch, each call to `pluck` is another hit of the db
task_ids = tasks.collect {|query| query.pluck(:id) }
tasks_ids.uniq!

@tasks = Tasks.where(id: tasks_ids)

Answer 2

So I solved it, it is supper sloppy however.

first I wrote a method

def add_res(ar_obj)
    ar_obj.each do |o|
        res += o.tasks
    end
    return res
end

then I wrote my search logic like so

if !search_params[:user].empty?
    query = add_res(User.where('handle LIKE ?', "%#{search_params[:user]}%"))
    @tasks.nil? ? @tasks=query : @tasks=@tasks&query
end
if !search_params[:client].empty?
    query = add_res(Client.where('name LIKE ?', "%#{search_params[:client]}%"))
    @tasks.nil? ? @tasks=query : @tasks=@tasks&query
end
if !search_params[:project].empty?
    query = add_res(Project.where('name LIKE ?', "%#{search_params[:project]}%"))
    @tasks.nil? ? @tasks=query : @tasks=@tasks&query
end
if !search_params[:activity].empty?
    query = add_res(Activity.where('name LIKE ?', "%#{search_params[:activity]}%"))
    @tasks.nil? ? @tasks=query : @tasks=@tasks&query
end
if @tasks.nil?
    @tasks=Task.all
end
@tasks=@tasks.uniq

If someone can provide a better answer I would be forever greatful