ZF2 - MySQL Regex for Whole word searches

Question

Given: You have a functioning substring-search in a ZF2 model. How do you do turn that into a whole-word search?

Answer 1

One method would be to use a MySQL REGEXP call in a Predicate Expression.

Where $select is an instance of Zend\Db\Sql\Select and $searchFor is your search term:

The original substring search might use a where like this...

$select->where(array(
    new Predicate\PredicateSet(
        array(
            new Zend\Db\Sql\Predicate\Like('tag', '%' . $searchFor . '%'),
            new Zend\Db\Sql\Predicate\Like('title', '%' . $searchFor . '%'),
        ),
        Zend\Db\Sql\Predicate\PredicateSet::COMBINED_BY_OR
    ),
));

...and whole-word version of the above is:

$select->where(array(
    new Predicate\PredicateSet(
        array(
            new Zend\Db\Sql\Predicate\Expression("tag REGEXP '([[:<:]]" . $searchFor ."[[:>:]])'"),
            new Zend\Db\Sql\Predicate\Expression("title REGEXP '([[:<:]]" . $searchFor ."[[:>:]])'"),
        ),
        Zend\Db\Sql\Predicate\PredicateSet::COMBINED_BY_OR
    ),
));

Note these are multi-field searches, so I've done a PredicateSet::COMBINED_BY_OR. It took me far too long to stop fooling around with \b in my regex. Hope this saves someone out there some time.