Last Updated: February 25, 2016
· ttoni

ActiveRecord search within a concatenation of to fields using an incomplete query

Our shop-like app has an Order and Client classes:

class Order < ActiveRecord::Base
  belongs_to :user

class Client < ActiveRecord::Base
  has_many :orders

And a Client has obviously a name and a surname among other fields:

Client(id: integer, name: string, surname: string, ...)

#Existing Order in our DB and corresponding Client
'Order#1 - John Smith'
'Order#2 - Anna Garcia'
'Order#3 - John Brown'
'Order#4 - Ann Richards'
'Order#5 - John Smith'
'Order#6 - John Smith'

Now we would like to retrieve the Orders purchased by the client 'John Smith'. Our query though should work in cases where name and/or surname are incomplete.

E.g.: john, smith, john smith, jo smith, john sm.

This would be one possible approach:

def search_orders
  @orders=Order.joins(client).where("clients.name || '  ' || clients.surname ILIKE :search", search:"%#{query.split().join('% ')}%")

Using query.split().join('% ') converts the queries as such:

  • john -> %john% ( Matches John Smith's AND John Brown's orders)
  • smith -> %smith% ( Matches John Smith's orders)
  • john smith -> %john% smith% ( Matches John Smith's orders)
  • jo smith -> %jo% smith% ( Matches John Smith's orders)
  • jo sm -> %jo% sm% ( Matches John Smith's orders)

-- Edit

In the case we would like to use reversed queries such as smi john, smith john. We only need to add an OR case to our SQL. E.g.

def search_orders
  @orders=Order.joins(client).where("clients.name || '  ' || clients.surname ILIKE :search OR clients.surname || ' ' || clients.name ILIKE :search", search:"%#{query.split().join('% ')}%")

Any other approaches?