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
– Dave Isaacs, Commented Aug 13, 2012 at 15:18
Interesting thoughts! I am using Postgres. The query successfully matches letters that are the last character in the name. — dmonopoly
– dmonopoly, Commented Aug 13, 2012 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
– Dave Isaacs, Commented Aug 13, 2012 at 19:41

Community · Accepted Answer · 2017-05-23 11:52:47Z

3

+50

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, 2017 at 11:52

CommunityBot

11 silver badge

answered Aug 13, 2012 at 15:37

Jesse Wolgamott

40.3k4 gold badges86 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dmonopoly Over a year ago

This did it - thanks! I would also like to thank @davidrac below for the help on #2.

davidrac · Accepted Answer · 2012-08-02 15:54:08Z

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 2, 2012 at 15:54

answered Aug 2, 2012 at 15:47

davidrac

10.8k3 gold badges45 silver badges71 bronze badges

1 Comment

dmonopoly Over a year ago

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)

matsko · Accepted Answer · 2012-08-13 15:26:21Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related