0

Rails: 4.1.2

Database: PostgreSQL

For one of my queries, I am using methods from both the textacular gem and Active Record. How can I chain some of the following queries with an "OR" instead of an "AND":

people = People.where(status: status_approved).fuzzy_search(first_name: "Test").where("last_name LIKE ?", "Test")

I want to chain the last two scopes (fuzzy_search and the where after it) together with an "OR" instead of an "AND." So I want to retrieve all People who are approved AND (whose first name is similar to "Test" OR whose last name contains "Test"). I've been struggling with this for quite a while, so any help would be greatly appreciated!

Alexander
  • 3,959
  • 2
  • 31
  • 58

2 Answers2

0

I digged into fuzzy_search and saw that it will be translated to something like:

SELECT "people".*, COALESCE(similarity("people"."first_name", 'test'), 0) AS "rankxxx" 
FROM "people" 
WHERE (("people"."first_name" % 'abc'))  
ORDER BY "rankxxx" DESC

That says if you don't care about preserving order, it will just filter the result by WHERE (("people"."first_name" % 'abc'))

Knowing that and now you can simply write the query with similar functionality:

People.where(status: status_approved)
      .where('(first_name % :key) OR (last_name LIKE :key)', key: 'Test')

In case you want order, please specify what would you like the order will be after joining 2 conditions.

Hoang Phan
  • 2,146
  • 1
  • 15
  • 16
  • Thank you for the answer! This is very helpful! I would prefer to use hash conditions in the query, as they are neater and easier to modify, but if I have to, I will use the raw SQL. `fuzzy_search` orders the results from most relevant to least relevant using a "rankxxx" field. However, the "xxx" part after "rank" seems to be a unique number (maybe a timestamp) each time the query is run. Do you know how I would order the results by "rankxxx"? – Alexander Dec 07 '15 at 16:01
  • I'd rather say you cannot, the reason why the gem using random numbers is to prevent anybody from using it later on. You could use the same select as theirs (says `COALESCE(similarity ...) AS `), but it is not recommended. And you will face a trouble when evaluating the 2 conditions. If you care about order in this case, the best bet would be to select 2 times with activerecord, then aggregate the results whatever you want. You know it's not that much of a performance penalty, and easier to read and maintain. – Hoang Phan Dec 08 '15 at 03:19
  • Thank you for your reply. I don't believe performing two queries would help because I would not get the rank ordering. The results from the first query would be the only ones that will be ordered by rank. I have copied the query that is generated and modified it so that it would work with the `find_by_sql` method. I added in variable escapes to protect against SQL injection. My only problem now is that, with the raw SQL, I am not getting unique records. I have tried adding the DISTINCT keyword after the SELECT statement to no avail. – Alexander Dec 09 '15 at 05:34
  • I have it working! I would prefer to get the DISTINCT keyword working, but for now I am using the `uniq` method to filter out any duplicate records after the query has completed. I will post my answer within the next couple of days. I basically had to use raw SQL, as much as I didn't want to. – Alexander Dec 09 '15 at 06:29
0

After a few days, I came up with the solution! Here's what I did:

This is the query I wanted to chain together with an OR:

people = People.where(status: status_approved).fuzzy_search(first_name: "Test").where("last_name LIKE ?", "Test")

As Hoang Phan suggested, when you look in the console, this produces the following SQL:

SELECT "people".*, COALESCE(similarity("people"."first_name", 'test'), 0) AS "rank69146689305952314"
FROM "people"
WHERE "people"."status" = 1 AND (("people"."first_name" % 'Test')) AND (last_name LIKE 'Test')  ORDER BY "rank69146689305952314" DESC

I then dug into the textacular gem and found out how the rank is generated. I found it in the textacular.rb file and then crafted the SQL query using it. I also replaced the "AND" that connected the last two conditions with an "OR":

# Generate a random number for the ordering
rank = rand(100000000000000000).to_s

# Create the SQL query
sql_query = "SELECT people.*, COALESCE(similarity(people.first_name, :query), 0)" + 
            " AS rank#{rank} FROM people" +
            " WHERE (people.status = :status AND" +
            " ((people.first_name % :query) OR (last_name LIKE :query_like)))" + 
            " ORDER BY rank#{rank} DESC"

I took out all of quotation marks in the SQL query when referring to tables and fields because it was giving me error messages when I kept them there and even if I used single quotes.

Then, I used the find_by_sql method to retrieve the People object IDs in an array. The symbols (:status, :query, :query_like) are used to protect against SQL injections, so I set their values accordingly:

# Retrieve all the IDs of People who are approved and whose first name and last name match the search query.
# The IDs are sorted in order of most relevant to the search query.
people_ids = People.find_by_sql([sql_query, query: "Test", query_like: "%Test%", status: 1]).map(&:id)

I get the IDs and not the People objects in an array because find_by_sql returns an Array object and not a CollectionProxy object, as would normally be returned, so I cannot use ActiveRecord query methods such as where on this array. Using the IDs, we can execute another query to get a CollectionProxy object. However, there's one problem: If we were to simply run People.where(id: people_ids), the order of the IDs would not be preserved, so all the relevance ranking we did was for nothing.

Fortunately, there's a nice gem called order_as_specified that will allow us to retrieve all People objects in the specific order of the IDs. Although the gem would work, I didn't use it and instead wrote a short line of code to craft conditions that would preserve the order.

order_by = people_ids.map { |id| "people.id='#{id}' DESC" }.join(", ")

If our people_ids array is [1, 12, 3], it would create the following ORDER statement:

"people.id='1' DESC, people.id='12' DESC, people.id='3' DESC"

I learned from this comment that writing an ORDER statement in this way would preserve the order.

Now, all that's left is to retrieve the People objects from ActiveRecord, making sure to specify the order.

people = People.where(id: people_ids).order(order_by)

And that did it! I didn't worry about removing any duplicate IDs because ActiveRecord does that automatically when you run the where command.

I understand that this code is not very portable and would require some changes if any of the people table's columns are modified, but it works perfectly and seems to execute only one query according to the console.

Alexander
  • 3,959
  • 2
  • 31
  • 58