cakephp - building a query with a multi-word search string, searching multiple fields

Question

I have a model 'Mix' which has the fields (which I'd like to be searchable) 'name' 'description' and 'tag_words', as well as a boolean field 'published'. Users should be able to enter a multi-word search term and get results back for any of their words appearing in any of the searchable fields providing the 'published' field is set to 1

So far with the help of google and reading around on here I've got:

if ($this->request->is('post')) {
    $conditions = array();
    $search_terms = explode(' ', $this->request->data['Mix']['search']);
    foreach($search_terms as $search_term){
        $conditions[] = array('Mix.name Like' =>'%'.$search_term.'%');
        $conditions[] = array('Mix.description Like' =>'%'.$search_term.'%');
        $conditions[] = array('Mix.tag_words Like' =>'%'.$search_term.'%');
    }
    $searchResults = $this->paginate('Mix', array('conditions' => array('Mix.published'=>1, 'AND'=>array('OR' => $conditions))));
}

But since it returns nothing but loads of errors I can guess it's totally wrong. I've very confused, what is the correct syntax?

where did you wrote above code (in model or controller) & what error you got? — sandip
– sandip, Commented Mar 15, 2013 at 14:55
I put the code in the controller, I've posted the error in the comments to the answer below. Thanks for your help — Patrick Guinness
– Patrick Guinness, Commented Mar 15, 2013 at 15:36

drmonkeyninja · Accepted Answer · 2013-03-15 14:53:56Z

1

You don't use the AND index, it is already implied:-

$searchResults = $this->paginate('Mix', array('conditions' => array('Mix.published'=>1, 'OR' => $conditions)));

answered Mar 15, 2013 at 14:53

drmonkeyninja

8,5404 gold badges35 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Patrick Guinness Over a year ago

Hey, thanks for the response. But when I try that I'm getting the error: SQL Query: SELECT Mix.id, Mix.created_by, Mix.name, Mix.description, Mix.published, Mix.temporary, Mix.bgimage, Mix.bgcolor, Mix.tag_words, Mix.columns, Mix.rows, Mix.date_added, Mix.tile FROM homestartpage.mixes AS Mix WHERE conditions IN (1, Array) LIMIT 20

Patrick Guinness Over a year ago

oops, sorry, the error is: Column not found: 1054 Unknown column 'conditions' in 'where clause' then there is the query I posted above

Patrick Guinness Over a year ago

Ah, ok I was being stupid. I copied my code from elsewhere and didn't realise I needed to add the pagination to the controller. Got it sorted now. Sorry :(

Collectives™ on Stack Overflow

cakephp - building a query with a multi-word search string, searching multiple fields

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related