SELECT MATCH HAVING

2 posts by 2 authors in: Forums > CMS Builder
Last Post: November 5, 2012 (RSS)

Archived
Reply
Reply

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?

Re: [mdegive] SELECT MATCH HAVING

Archived
Reply
Reply

By gregThomas - November 5, 2012

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:

 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