Strategy For Like Or Partial Searches

[dalezak]

I'd like to do partial name searches on our User table for blind_index :name.

With our current database size, doing select like User.select { |u| u.name =~ /dale/i } takes over 5 seconds.

Is there an expression that could be used to help do partial matches?

Or perhaps use virtual attribute to store words in an array so I could search for dale or zak to find my user?

I'd like to keep the name field encrypted but searching has also become important so looking for a solution. Any help would be greatly appreciated!

[dalezak]

Curious, would something like the following work allowing for searching by first or last name?

Current Index

  attr_encrypted :name, key: [ENV['ENCRYPTION_KEY']].pack("H*"), attribute: "encrypted_name"
  blind_index :name, key: [ENV['BLIND_INDEX_KEY']].pack("H*"), attribute: "name", bidx_attribute: "encrypted_name_bidx"

New Indexes

 blind_index :name_first, key: [ENV['BLIND_INDEX_KEY']].pack("H*"), attribute: "name", bidx_attribute: "encrypted_name_first_bidx", expression: ->(v) { v.downcase.split.first }
 blind_index :name_last, key: [ENV['BLIND_INDEX_KEY']].pack("H*"), attribute: "name", bidx_attribute: "encrypted_name_last_bidx", expression: ->(v) { v.downcase.split.last }

[dalezak]

Woah, implemented this and it works surprisingly well!

Perhaps the readme could include an example like this so it helps others? Closing issue!

[mdsjs]

hi @dalezak , maybe can share the full implementation about set the index and how to search it?

[dalezak]

@mdsjs here's how I define my blind_indexes

  attr_encrypted :name, key: [ENV['ENCRYPTION_KEY']].pack("H*"), attribute: "encrypted_name"
  blind_index :name, key: [ENV['BLIND_INDEX_KEY']].pack("H*"), attribute: "name", bidx_attribute: "encrypted_name_bidx", expression: ->(n) { n.downcase }
  
  attribute :name_first, :string
  blind_index :name_first, key: [ENV['BLIND_INDEX_KEY']].pack("H*"), attribute: "name", bidx_attribute: "encrypted_name_first_bidx", expression: ->(n) { n.downcase.split.first }
  
  attribute :name_last, :string
  blind_index :name_last, key: [ENV['BLIND_INDEX_KEY']].pack("H*"), attribute: "name", bidx_attribute: "encrypted_name_last_bidx", expression: ->(n) { n.downcase.split.last }

And here is my search scopes

scope :for_search, ->(query) { where(email: query).or(where(name: query.downcase)).or(where(name_first: query.downcase)).or(where(name_last: query.downcase)) if query.present? }

So if you have a user Tom Jones, the following searches would find him.

User.for_search("tom.jones@gmail.com")
User.for_search("Tom Jones")
User.for_search("Tom")
User.for_search("Jones")

[mdsjs]

many thanks @dalezak !