How Do I Write A Query In Ruby Using Variables WIthin A Where Clause (PostgreSQL)

Question

This is what I want to accomplish without using a gem hopefully. I have a search function on one of my views where I desire to allow people to enter a first and last name in a single field, have my class method search both first and last names and produce a list. I currently have the following ugly code where it will do a full text search on both fields. Have mercy on me :) I am new to SQL and PostgreSQL. I have only done basic queries.

  def self.text_search(query)
    if query.present?
      q1, q2, q3, q4 = query.split(' ')
      case
      when q4 != nil
        where('first_name @@ :q1 OR first_name @@ :q2 OR first_name @@ :q3 OR first_name @@ :q4 OR last_name @@ :q1 OR last_name @@ :q2 OR last_name @@ :q3 OR last_name @@ :q4', q1: q1, q2: q2, q3: q3, q4: q4)
      when q3 != nil
        where('first_name @@ :q1 OR first_name @@ :q2 OR first_name @@ :q3 OR last_name @@ :q1 OR last_name @@ :q2 OR last_name @@ :q3', q1: q1, q2: q2, q3: q3)
      when q2 != nil
        where('first_name @@ :q1 OR first_name @@ :q2 OR last_name @@ :q1 OR last_name @@ :q2', q1: q1, q2: q2)
      else
        where('first_name @@ :q1 OR last_name @@ :q1', q1: q1)
      end
    else
      scoped
    end
  end

This code works where it searches for the entire word in both fields and produces all records that match. However what I would like to do is to be able to search columns starting with the values entered in the search box. For example if someone enters 'Pat' the query will select Pat, Patrick, Patrice, Patricia, etc. I'm sure that there is a better way to write even the code I have included where I can list all the values I want to check for first_name and last_name (or at least I hope there is).

I have been doing a lot of web searches today regarding this. I found a lot of information about various aspects of this. I saw how to create a regular expression using variables. This one was obvious to me. My problem was figuring out how to pass that information into my where clause. I tried for example the following code for all four fields.

qr1 = /^#{q1}/

Then I did a similar where clause where I checked both first_name and last_name for all four values. My queries did not return any rows.

where ('first_name ~ :qr1', qr1: qr1)

I also saw solutions about checking a substring comparing it to an array. The problem I see with that solution is that you have to know how long the value entered is before you can be successful with that. Here is a Stack Overflow post with an example:

Select where first letter in a range ( PostgreSQL )

I have also watched RailsCasts #343 (Pro) where it discusses to_tsvector and plainto_tsquery but frankly I got lost. Maybe this is my answer but I could sure use some help. It is clear as mud to me.

Answer 1

def self.text_search(query)
  if query.present?
    arr = query.split(' ').map { |q| "%#{q}" }

    where('first_name ~~* ANY(ARRAY[:arr]) OR last_name ~~* ANY(ARRAY[:arr])', arr: arr)
  else
    scoped
  end
end