3

So I've been trying to implement a search feature where by a user can enter a keyword and look up users. The search method is supposed to look up exact word matches within the fields of the users e.g first name, last name, job etc

I have tried icontains but that would also match within the words e.g if the user enters 'a' the search would return anything that contains an 'a'. (This was what I was originally doing before I decided to fix the issue)

I tried iexact but that would obviously give an exact match and if the user was to enter 'john' it would skip the user whose first name was 'john doe' or something.

So after quite a bit of googling I found the two stackoverflow threads

django filter icontains match whole words only

Whole-word match only in Django query

Since I am using postgresql I switched to using \y instead of \b. Now the query I am currently using is

results = User.objects.filter(first_name__iregex=r"\y{0}\y".format(first))

I have also tried, and the other methods listed in the two threads I mentioned.

results = User.objects.filter(first_name__iregex=r"\y%s\y"%first)

The problem is that it doesn't return anything. While debugging I have gone and checked what query is being generated, as far as I know it's seems correct.

WHERE `LinkedApp_user`.`first_name` REGEXP \yahsan\y

My database currently has about 5 users with one of them having the first name "ahsan" while another having first name "ahsan saleem". However this query returns nothing. I would really appreciate any help.

EDIT: I tried to switch to SQLite and change \y with \b and the search seems to have started working. However I can't work with sqlite, I would really appreciate if someone could guide me.

Another EDIT: I have solved my issue with postgresql by switching db. A stupid mistake anyway. The question is how would you make this work with mysql, all I have tried has failed.

cezar
  • 11,616
  • 6
  • 48
  • 84
Ahsan Saleem
  • 43
  • 1
  • 7
  • `"Since I am using postgresql I switched to using /y instead of /b."` - Have you *tried* using `\b` to see if it really isn't working? – yuvi Nov 15 '13 at 19:37
  • Sorry about the typo, as I said it's not working. – Ahsan Saleem Nov 15 '13 at 21:33
  • Yes the \b works with Sqlite, I tested it too. Have you tried running the query without django and see if it works? Maybe postgresql is case-sensitive, have you tried searching `Ahsan` instead? – yuvi Nov 15 '13 at 22:46
  • I switched my db and it's now working. Still can't make it work with my local mysql though. \y works with postgresql. and case wouldn't matter as I'm using iregex not regex. Thanks for the help, but would appreciate if you could point out why it won't work with mysql – Ahsan Saleem Nov 15 '13 at 23:41
  • Just to let you guys know that as of yet I found no way to make it work on MYSQL. Works fine on Sqlite and Postgresql! – Ahsan Saleem Apr 23 '14 at 23:01
  • MySQL? Wasn't the problem with Postgresql? – yuvi Apr 24 '14 at 06:59
  • It was but I was able to resolve it simply by switching by DB to another one (also postgresql). But I was never able to run it locally on Mysql so I was wondering if there was a solution or not? – Ahsan Saleem Apr 25 '14 at 07:28
  • `\b` should work on MySQL – yuvi Apr 27 '14 at 06:37

1 Answers1

4

In MySQL the word boundaries are matched by [[:<:]] and [[:>:]] (http://dev.mysql.com/doc/refman/5.7/en/regexp.html)

caujka
  • 121
  • 4