Using match=words attribute in listings shortcode

listings-shortcode-word-match.md

All searches in JReviews use LIKE to retrieve results. To use an exact word match, it would be necessary to use REGEXP because there aren't any fulltext indexes.

The following filter allows adding the match=word attribute to the listings shortcode to perform a word match. Example usage:

[jreviews type="listings" custom_params="keywords=you" match="word" limit="3"]

The above will match "you", but not "yourself", "yours", etc.

If you are unfamiliar with JReviews hooks, read the Getting Started with hooks article in the documentation.

The REGEXP syntax for matching words is different in MySQL 8 than in previous versions. Check the code below and if you are using MySQL 8, then comment the line for earlier versions and uncomment the line for MySQL 8.

Clickfwd\Hook\Filter::add('pre_get_listings_listings_module_query', function($listingsRepository, $params) 
{
	$customParams = $params['params']['module']['custom_params'] ?? '';

	$match = $params['params']['module']['match'] ?? '';

	if (empty($customParams)) {
		return $listingsRepository;
	}
	
	if ($match !== 'word') {
		return $listingsRepository;
	}

	$listingModel = $listingsRepository->getModel();

	$conditions = $listingModel->conditions;
	
	foreach ($conditions as $key => & $value) {
		preg_match_all("/LIKE '%([\w]+)%'/", $value, $matches);
		if (! $matches) continue;

		foreach(array_unique($matches[1]) as $word) {
			// MySQL < v8
			$value = str_replace("LIKE '%{$word}%'", 'REGEXP "[[:<:]]'.$word.'[[:>:]]"', $value);
      
			// MySQL v8+
			// $value = str_replace("LIKE '%{$word}%'", 'REGEXP "\\\\b'.$word.'\\\\b"', $value);
		}
	}

	$listingModel->conditions = $conditions;
	
  	return $listingsRepository;
});