Finding records that match a set of search terms is almost a must-have on most web applications. Even when there is a good number of more in-depth, complex search solutions, sometimes a simple search is all we need.
This recipe shows how to implement a LIKE-based
search to find records that match some terms.
We need some sample models and data to work with. Follow the Getting ready section of the recipe, Performing GROUP and COUNT queries.
If we want to find all posts that have the word Post 1
or the word Post 2
, either in its title, or post, we do:
$posts = $this->Post->find('all', array( 'fields' => array('Post.id', 'Post.title'), 'conditions' => array('or' => array( array('Post.title LIKE ?' => '%Post 1%'), array('Post.body LIKE ?' => '%Post 1%'), array('Post.title LIKE ?' => '%Post 2%'), array('Post.body LIKE ?' => '%Post 2%'), )), 'recursive' => -1 ));
The preceding statement will produce the following result:
array( 'Post' => array( 'id' => 1, 'title' => 'John's Post 1' ), 'Post' => array( 'id' => 2, 'title' => 'John's Post 2' ), 'Post' => array( 'id' => 5, 'title' => 'Jane's Post 1' ), 'Post' => array( 'id' => 6, 'title' => 'Jane's Post 2' ) )
LIKE-based
conditions are like any other model find condition, except that they are specified in a special form: they become part of the key of the condition and use the character ?
to tell where the actual value will be inserted, the value being an actual LIKE
expression. Therefore, the following condition:
array('Post.title LIKE ?' => '%term%')
will be evaluated to SQL like so:
`Post`.`title` LIKE '%term%'
As the LIKE
expression is specified as an array index, it's important to note that we need to wrap each expression in an array of its own, avoiding the override of a previous expression. To illustrate this, let us add another condition for the Post.title
field.
array( 'Post.title LIKE ?' => '%term%', 'Post.title LIKE ?' => '%anotherTerm%' )
This would be translated to the following SQL expression:
`Post`.`title` LIKE '%anotherTerm%'
Naturally, the second index is overriding the first, because they are both the same. We therefore have to wrap both expressions in an array, to avoid overriding the already used indexes:
array( array('Post.title LIKE ?' => '%term%'), array('Post.title LIKE ?' => '%anotherTerm%') )
which would be translated to the following SQL statement:
`Post`.`title` LIKE '%term%' OR `Post`.`title` LIKE '%anotherTerm%'
18.221.123.73