16

Consider these three "haystack" strings:

a) foo bar

b) welcome to foo bar industries

c) foo barer

And now my "needle":

foo bar

(Heh)

I would like my filter to match my needle with haystack strings a & b but not c. I have tried:

$collection->addAttributeToFilter('name', array('like' => '%'.$needle.'%'));

But the above matches with c.

I also tried:

$collection->addAttributeToFilter('name', array('like' => '% '.$needle.' %')); // Note the spaces

The above only matches with b.

Any help is much appreciated.

37

Give this a try and see if it fits:

$collection->addAttributeToFilter('name', array(
    array('like' => '% '.$needle.' %'), //spaces on each side
    array('like' => '% '.$needle), //space before and ends with $needle
    array('like' => $needle.' %') // starts with needle and space after
));

Passing the second parameter as an array of arrays will concatenate the conditions using OR

  • That works :) Didn't know about the array trick, thanks. – beingalex Nov 22 '13 at 12:15
  • With custom / non-eav models addAttributeToFilter should be replace with addFieldToFilter. – jvalanen Jul 5 '16 at 18:44
  • Is there a way to determine which array returned a result? I am using this method to search a custom model collection (name,description,alternate_name fields) and I'd like to know if the result was hit because of the description (to include additional UI element on search results). – pspahn Jan 8 '18 at 1:12
  • 3
    @pspahn Not a straight forward one. You can loop through the results after the select is executed and check if the needle is found in any of the fields you want. – Marius Jan 8 '18 at 8:21
9

A possible solution is to use REGEXP instead of LIKE:

$collection->addAttributeToFilter('name', array('regexp' => '[[:<:]]'.$needle.'[[:>:]]'));

From the MySQL documentation:

[[:<:]], [[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

Note that if $needle can contain characters that have special meanings in POSIX regular expressions, you need to escape them. See also: https://stackoverflow.com/questions/4024188/php-function-to-escape-mysql-regexp-syntax

2

The accepted answer doesn't work properly. It's checking only space before and after the text.

Suppose, you have following list

  • Jacket
  • Summer Jacket
  • Winter Jacket

Now if you try to search with jacket using the accepted answer, it will return only Summer Jacket and Winter Jacket

I think following solution is better

$collection->addAttributeToFilter('name', array('like' => '%' . $needle. '%'));
  • It would not cover case (c) from the question (i.e. also match "jacketing"). Instead, a fourth condition ['eq' => $needle] could be added to find exact matches (or better, use the regex solution which finds all boundaries, not only spaces) – Fabian Schmengler Mar 29 '18 at 11:57
  • above code is able to find the exact matches. I checked it – Dinesh Yadav Mar 29 '18 at 11:59
  • I did not doubt that. But it finds more, which it should not. At least if you have the same requirements as in the original question: Do not find "foo barer" when searching "foo bar" – Fabian Schmengler Mar 29 '18 at 14:37
  • Yes, you are right. The accepted answer was not working for me properly so I suggested the simple one-line filter for getting the likewise items. – Dinesh Yadav Mar 30 '18 at 4:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.