CODEWITHSUNDEEP
ruby-on-railspostgresqlsearchfull-text-search
rustyshackleford
rustyshackleford

Reputation: 731

Dealing with special characters in a text search using Ruby on Rails

I'm trying to achieve a specific search in my application, and I've run into a bit of trouble.

I want the first results in my search to contain the phrase that was searched for first, instead of just containing it.

For example, if I search for parts in my application, I get these results:

Add-On Windshield Wiper Kit
Turn Signal / Headlight / Windshield Wiper/Washer Combination Lever Set
Turn Signal / Headlight / Windshield Wiper/Washer Combination Switch and Lever Set
Windshield Wiper / Washer Circuit Breaker
Windshield Wiper / Washer Dash Indicator Light
Windshield Wiper Arm

Whereas I'm trying to achieve something like this:

Windshield Wiper Arm
Windshield Wiper / Washer Circuit Breaker
Windshield Wiper / Washer Dash Indicator Light
Add-On Windshield Wiper Kit
Turn Signal / Headlight / Windshield Wiper/Washer Combination Lever Set
Turn Signal / Headlight / Windshield Wiper/Washer Combination Switch and Lever Set

Notice how Windshield Wiper Arm is the first result because it contains the search parameters (which is simply "Windshield Wiper") and a follow-up word that begins with the letter A.

Anyway, here's my model:

class Pcdb::Part < CatalogsRecord
  include PgSearch
  self.table_name = "parts"
  belongs_to :parts_description
  pg_search_scope :search_for_parts, against: :part_terminology_name, using: { tsearch: { dictionary: "simple", normalization: 2 } }
end

And the relevant part of my controller:

Pcdb::Part.where('lower(parts.part_terminology_name) like ?', "%#{search}%").reorder("parts.part_terminology_name ASC")

In this particular instance, the search variable is Windshield Wiper.

Thanks in advance!

Upvotes: 0

Views: 446

Answers (1)

Nick
Nick

Reputation: 147166

You could add a conditional to your ordering expression, checking whether the name started with the search term, and ordering those results first i.e. (as SQL)

ORDER BY 
    CASE WHEN parts.part_terminology_name LIKE 'Windshield Wiper%' THEN 0 ELSE 1 END,
    parts.part_terminology_name

If you want to get more sophisticated about ordering, you could use regex to ensure that parts with the phrase followed by a word sorted before those with the phrase followed by a symbol (i.e. Windshield Wiper Arm before Windshield Wiper / Washer Circuit Breaker)

ORDER BY 
    CASE WHEN parts.part_terminology_name = 'Windshield Wiper' THEN 0 ELSE 1 END,
    CASE WHEN parts.part_terminology_name ~ '^Windshield Wiper [a-z]' THEN 0 ELSE 1 END,
    CASE WHEN parts.part_terminology_name LIKE 'Windshield Wiper%' THEN 0 ELSE 1 END,
    parts.part_terminology_name

Demo on SQLFiddle

Upvotes: 1

Related Questions