SELECT MATCH HAVING
2 posts by 2 authors in: Forums > CMS Builder
Last Post: November 5, 2012 (RSS)
By mdegive - November 2, 2012
I am sing the MYSQL MATCH to search for keywords within the content field. My select (generated through cmsbuilder List()) looks like this:
SELECT SQL_CALC_FOUND_ROWS `listings`.*, MATCH(headline,content) AGAINST('2 bedroom' IN BOOLEAN MODE) AS relevance FROM `cms_listings` as `listings` WHERE (MATCH(headline,content) AGAINST('2 bedroom' IN BOOLEAN MODE) HAVING relevance > 0.2) ORDER BY area, relevance DESC, date_available DESC
MYSQL gives a syntax error at the "HAVING". If I remove the parenthesis after WHERE and before HAVING it works.
CMS Builder is inserting these parentheses. Is there a way to avoid this?
SELECT SQL_CALC_FOUND_ROWS `listings`.*, MATCH(headline,content) AGAINST('2 bedroom' IN BOOLEAN MODE) AS relevance FROM `cms_listings` as `listings` WHERE (MATCH(headline,content) AGAINST('2 bedroom' IN BOOLEAN MODE) HAVING relevance > 0.2) ORDER BY area, relevance DESC, date_available DESC
MYSQL gives a syntax error at the "HAVING". If I remove the parenthesis after WHERE and before HAVING it works.
CMS Builder is inserting these parentheses. Is there a way to avoid this?
Re: [mdegive] SELECT MATCH HAVING
Hi,
The getRecords function has an array option called 'having' that would allow you to add the HAVING statement outside of the where statement. You can implement it something like this:
Thanks!
The getRecords function has an array option called 'having' that would allow you to add the HAVING statement outside of the where statement. You can implement it something like this:
list($newsRecords, $newsMetaData) = getRecords(array(
'tableName' => 'listings',
'loadUploads' => true,
'allowSearch' => false,
'having' => "relevance > 0.2",
'orderBy' => 'area, relevance, date_available DESC'
));Thanks!
Greg Thomas
PHP Programmer - interactivetools.com
PHP Programmer - interactivetools.com