cakephp match against order by doesn't work

Question

I am trying to order my pagination result. But it doesn't work.

$this->paginate = array(
                'limit' => 15,
                'fields' => array('*', "MATCH (headline, author, short_headline, subline, teaser, text) AGAINST ('$var') AS score"),
                'conditions' =>  "MATCH(headline, author, short_headline, subline, teaser, text) AGAINST('$var' IN BOOLEAN MODE)",
                'order' => array('score' => 'desc')
            );

The query looks like:

SELECT *, MATCH (`headline`, `author`, `short_headline`, `subline`, `teaser`, `text`) AGAINST ('herz tod') AS `score`, `Story`.`id` FROM `fates`.`stories` AS `Story` WHERE MATCH(headline, author, short_headline, subline, teaser, text) AGAINST('herz tod' IN BOOLEAN MODE) LIMIT 15

and order by score is gone! What i am doing wrong? thanks for help!

score 1 · Accepted Answer · answered Mar 10 '14 at 21:33

Try making score a virtualField before you try to paginate in order to use it to order your pagination. (There may be other ways to get it to order by score, but I'm not aware of them.)

$this->Model->virtualFields['score'] = "MATCH (headline, author, short_headline, subline, teaser, text) AGAINST ('" . Sanitize::escape($var) . "')";

I'd like to also point out, just in case, that your code (and the code I posted above for that matter) is vulnerable to SQL injection, if you cannot assume $var to be trustworthy data, like if $var comes from user input.

Thank you Kai! This works fine. And yes, i will use this for a site search. You are right. I have to validate $var through the Model to prevent SQL injection. — Paul, Mar 11 '14 at 19:35

cakephp match against order by doesn't work

1 Answers1