yii2 query match partial word

Question

So i'm working a little search tool for a website but I ran into an little issue, when I enter a word and if this word is present it will return the results, but when i search on this word and add a extra character to it wil not return any results.

When I search on the word 'tool' it will return the results as this word is present in the DB, but when I search on 'tools' it will not return anything, as tools is not present in the DB.

word present in DB = 'tool'
search in DB with word 'tool' = returns results
search in DB with word 'tools' = returns noting as tools is not present

When I search on the word 'tools' it should also return any results that matches with 'tool'

$query->andFilterWhere([
    'or',
    ['like', 'product.name', $this->searchValue],
    ['like', 'product.desc', $this->searchValue],
]);

ok, probably beacuse your query generate somethin like this SELECT * FROM table WHERE product.name LIKE '%tools%'. You need ['like', 'product.name', '%tools', false] will generate name LIKE '%tools' — Sfili_81, Oct 29 '18 at 13:21
I have tried wildcards, but this will return less results and even no results when I use tools — user759235, Oct 29 '18 at 13:37
Why would you expect that `tools` will match the result containing `tool` or 'tooll', 'toolo'? — Paul, Oct 30 '18 at 05:55
Possible duplicate of [How to find similar results and sort by similarity?](https://stackoverflow.com/questions/3338889/how-to-find-similar-results-and-sort-by-similarity) — rob006, Oct 30 '18 at 10:08

score 0 · Answer 1 · answered Oct 29 '18 at 13:46

0

You could run the search string through the singularize inflector.

$value = yii\helpers\Inflector::singularize($this->searchValue);
$query->andFilterWhere([
    'or',
    ['like', 'product.name', $value],
    ['like', 'product.desc', $value],
]);

Obviously this will only work for single word plurals.

Ed.

answered Oct 29 '18 at 13:46

Ed209

821
5
8

Well this will still not return the results – user759235 Oct 29 '18 at 15:15
Yes it will, in your example, if `$this->searchValue` is 'tools' `$value` will be 'tool'. – Ed209 Oct 29 '18 at 15:25
Yes thats why I mentioned it would only work for plurals, you will probably need to have a combination of something like soundex and a full-text index on the DB to get near misses. – Ed209 Oct 29 '18 at 15:49

score 0 · Answer 2 · answered Oct 30 '18 at 05:06

0

You can search using below query :

$query->andFilterWhere(
 "product.name LIKE CONCAT('%',$this->searchValue,'%') OR
  product.desc LIKE CONCAT('%',$this->searchValue,'%')"
);

answered Oct 30 '18 at 05:06

Yasin Patel

5,624
8
31
53

yii2 query match partial word

2 Answers2