rails with jquery token-input: first character ignored in search

Question

UsersController's index:

@users = current_user.other_users.where("first_name like ?", "%#{params[:q]}%")

1) Search works fine in the text field except that the first character isn't detected.

Example: If I search with "J" for "John" I won't get any results, but if I do "o" I will get "John" as I should.

2) I'm wondering how you add an additional search condition above for last_name...? I thought something like

.where("first_name like ? or last_name like ?", "%#{params[:q]}%")

would work, but it doesn't...

Help appreciated!

What database are you using? Maybe (though I do not know why this would occur, it goes against all docs I can find) that the `%` characters in your search are not matching zero length. Meaning that `"%J%"` won't match the names starting with J since it expects another character in front of it. Does your query match letters that are the last character in the name? — Dave Isaacs, Aug 13 '12 at 15:18
Interesting thoughts! I am using Postgres. The query successfully matches letters that are the last character in the name. — dmonopoly, Aug 13 '12 at 18:24
If you can match the last letter of a name, then my suggestion is probably NOT the cause of the problem. But you can try dropping the first `%` from the where clause, just to see what happens. — Dave Isaacs, Aug 13 '12 at 19:41

score 3 · Accepted Answer · edited May 23 '17 at 11:52

3

I'm betting you're using Postgres as your database, where LIKE clauses are case-sensitive. To search case-insensitive in Postgres:

.where("first_name ILIKE ? or last_name ILIKE ?", "%#{params[:q]}%")

This SO has plenty of background: How do you write a case insensitive query for both MySQL and Postgres?

Other alternatives:

https://github.com/texticle/texticle
store lowercase version of full_name and search on that field

edited May 23 '17 at 11:52

Community

1
1

answered Aug 13 '12 at 15:37

Jesse Wolgamott

40,197
4
83
109

This did it - thanks! I would also like to thank @davidrac below for the help on #2. – dmonopoly Aug 14 '12 at 01:52

davidrac · Answer 2 · 2012-08-02T15:54:08.313

1

I'm not sure why this does not match. Maybe you should add ' ' around the parameter: "first_name like '?'"
I think you should have two params in there:

.where("first_name like ? or last_name like ?", "%#{params[:q]}%", "%#{params[:q]}%")

edited Aug 02 '12 at 15:54

answered Aug 02 '12 at 15:47

davidrac

10,723
3
39
71

1

1 is no good - putting '' around the ? just doesn't let the search work at all. However, 2 works great - thanks a lot for that! (If you ever come up with another idea for 1, please let me know) – dmonopoly Aug 02 '12 at 16:17

score 0 · Answer 3 · answered Aug 13 '12 at 15:26

The reason why the first letter isn't being detected is because you have a % infront of the first and last name columns within the where statement. So since there is nothing behind J then that's why %John% won't work.

You will need to look for both before and after statements.

This would work.

q1 = params[:q].to_s + '%'
q2 = '%' + q1
where(["first_name LIKE ? OR last_name LIKE ? OR first_name LIKE ? OR last_name LIKE", q1,q1,q2,q2])

Then put UNIQUE in your SQL statement to make them all unique rows.

It's better to use a fulltext search index for this kind of stuff anyway since the LIKE operations aren't indexed for front and back operations (only front). This is assuming you're using MySQL as your DB backend.

rails with jquery token-input: first character ignored in search

3 Answers3