Last Updated: February 25, 2016
·
1.026K
· 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
end

class Client < ActiveRecord::Base
  has_many :orders
end

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:

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

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.

#app/controllers/searches_controller.rb
...
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('% ')}%")
end

Any other approaches?