Rails gem rails3-jquery-autocomplete: How do I query multiple fields

Question

I'm using the rails3-jquery-autocomplete gem found here: http://github.com/crowdint/rails3-jquery-autocomplete

The instructions are clear for how to query a single attribute of a model and I am able to make that work without a problem.

My Person model has two attributes that I would like to combine and query, however. They are first_name and last_name. I would like to combine them into a pseudo-attribute called full_name. Currently, I receive this error:

ActiveRecord::StatementInvalid (SQLite3::SQLException: no such column: full_name: SELECT     "people".* FROM       "people"  WHERE     (LOWER(full_name) LIKE 'cla%') ORDER BY  full_name ASC LIMIT 10):

There is no full_name attribute of the Person model, though I have the following method in the Person model file:

def full_name
  "#{self.first_name} #{self.last_name}"
end

How do I modify the Person model file so that calls to full_name queries the database to match a combination of first_name and last_name?

Answer 1

Your pseudo attribute works only on records already retrieved, but it has no bearing on searching for records. Probably the easiest solution is a named named scope like:

 scope :search_by_name, lambda { |q|
   (q ? where(["first_name LIKE ? or last_name LIKE ? or concat(first_name, ' ', last_name) like ?", '%'+ q + '%', '%'+ q + '%','%'+ q + '%' ])  : {})
 }

Thus, a call like:

Person.search_by_name(params[:q]) 

will return an appropriate result set. It will also return all entries if no param was passed (or more specifically, this scope will add nothing extra), making it an easy drop-in for the index action.

Answer 2

Sadly, the scope method mentioned above didn't work for me. My solution was to simply overwrite the get_autocomplete_items method (formerly get_items).

For what it's worth, there is a MySQL function (other db's have it as well, but we're talking MySQL for the moment) that is better suited to the type of concatenation you're using:

def get_autocomplete_items(parameters)
  items = Contact.select("DISTINCT CONCAT_WS(' ', first_name, last_name) AS full_name, first_name, last_name").where(["CONCAT_WS(' ', first_name, last_name) LIKE ?", "%#{parameters[:term]}%"])
end

MySQL's CONCAT_WS() is intended to join strings together with some sort of separator and is ideal for full names.

This chunk of code basically says return the "first last" formatted names of contacts that match whatever the user is searching by when we pair up the database's contact records by a concatenated pairs of first and last names. I feel it's better than the SQL statement above since it does a full search that will match first AND/OR last name in one statement, not three OR statements.

Using this "hn sm" would match "John Smith" since indeed "hm sm" is LIKE "John Smith". Furthermore, it has the added benefit of also returning the concatenated first and last name of each contact. You may want the full record. If that's the case, remove the select() query from the line above. I personally had the need for the user to search for a name and have an autocomplete field return all possible matches, not the records.

I know this is a bit late, but I hope it helps someone else!

Answer 3

---

Full implementation of multiple-field autocomplete :

---

Following my comment, my solution to integrate into jquery-autocomplete was to have a custom implementation of the "internal" autocomplete.

1. Query the database

If you're using ActiveRecord, you can use DGM's solution for your named_scope

If you're using Mongoid, you can use this syntax instead:

scope :by_first_name, ->(regex){ 
    where(:first_name => /#{Regexp.escape(regex)}/i)
}
scope :by_last_name, ->(regex){
    where(:last_name => /#{Regexp.escape(regex)}/i)
}
scope :by_name, ->(regex){ 
    any_of([by_first_name(regex).selector, by_last_name(regex).selector])
}

EDIT : if you want a stronger autocomplete that can handle accents, matching parts of text, etc; you should try the mongoid text index. Credits to the original answer there

index(first_name: 'text', last_name: 'text')

scope :by_name, ->(str) {
  where(:$text => { :$search => str })
}

And don't forget to build the indexes after adding that rake db:mongoid:create_indexes

So you can basically do User.by_name(something)

2. Create an autocomplete action in your controller

Because the one provided by jquery-autocomplete... ain't gonna do what we want.

Note that you'll have to convert the result to JSON so it can be used in the frontend jquery-autocomplete. For this I have chosen to use the gem ActiveModel::Serializer, but feel free to use something else if you prefer, and skip step 3

In your controller :

def autocomplete
    @users = User.by_name(params[:term])
    render json: @users, root: false, each_serializer: AutocompleteSerializer
end

3. Reformat the response

Your serializer using the gem activemodel:

I provided the link to the 0.9 version, as the master mainpage doesn't contain the full documentation.

class AutocompleteSerializer < ActiveModel::Serializer
  attributes :id, :label, :value

  def label
    object.name
    # Bonus : if you want a different name instead, you can define an 'autocomplete_name' method here or in your user model and use this implementation
    # object.respond_to?('autocomplete_name') ? object.autocomplete_name : object.name
  end

  def value
    object.name
  end

4. Create a route for your autocompletion

In your routes :

get '/users/autocomplete', to: 'users#autocomplete', as: 'autocomplete_user'

5. Have fun in your views

Finally in your views you can use the default syntax of jquery-rails, but remember to change the path !

<%= form_tag '' do
  autocomplete_field_tag 'Name', '', autocomplete_user_path, :id_element => "#{your_id}", class: "form-control"
end %>

RQ : I used some 2-level deep nested forms, so it was a bit tricky to get the right id element your_id. In my case I had to do somethig complicated, but most likely it will be simple for you. You can always have a look at the generated DOM to retrieve the field ID

Answer 4

This is a hack and I would very much like this function to be included in the gem but as slant said overwriting the get_autocomplete_items works as he wrote it but it will only return first_name and last_name from the model column. In order to restore functionality that frank blizzard asked for you also need to return the id of the row.

items = Contact.select("DISTINCT CONCAT_WS(' ', first_name, last_name) AS full_name, first_name, last_name, id").where(["CONCAT_WS(' ', first_name, last_name) LIKE ?", "%#{parameters[:term]}%"])

The difference between mine and slant's answer is id as the last argument of the select method. I know it is late but I hope it helps somebody in the future.

If you don't need the functionality of comparing your search string against the concatenated string and are trying to just do a query on three separate columns you can use this fork of the gem : git://github.com/slash4/rails3-jquery-autocomplete.git. Oh also that fork will only work with ActiveRecord which is probably why they didn't pull it.

Answer 5

To perform a case-insensitive search using @Cyril's method for rails4-autocomplete, I did a slight modification to the named scope @DGM provided

 scope :search_by_name, lambda { |q|
   q.downcase!
   (q ? where(["lower(first_name) LIKE ? or lower(last_name) LIKE ? or concat(lower(first_name), ' ', lower(last_name)) like ?", '%'+ q + '%', '%'+ q + '%','%'+ q + '%' ])  : {})
 }

This converts the record's entry to lowercase and also converts the search string to lowercase as it does the comparison