JomSocial Full-Text Search - Recipe

This is a Recipe to add a Full Text Search dialog to your JomSocial site.

At the end, we get more results, adding a powerful full-text search. It also can be combined with other tables or search operators.

Source: Full-text search - Wikipedia

Our original idea was to replace the original search, based on SQL 'LIKE' syntax (low performance) to perform a SQL 'MATCH()' (high performance full-text search).

Steps:

1Add the full-text search index to your tables (Eg: jos_community_fields_values). See: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

2Change the Joomla User Search plugin, to add the MATCH syntax
    $query    = '(SELECT j.id AS id, j.name AS title, c.status AS text,'
. ' "" AS created, "'.JText::_( 'Users' ).'" AS section, "2" AS browsernav'
. ' FROM ' . $db->nameQuote('#__community_users') . ' AS c'
. ' INNER JOIN ' . $db->nameQuote('#__users') . ' AS j ON j.id = c.userid'
. ' WHERE '
. $where
. ' AND j.block = ' . $db->quote(0)
. $usersFilter . ' GROUP BY j.id) UNION '
. '(SELECT j.id AS id, j.name AS title, c.status AS text,'
. ' "" AS created, "'.JText::_( 'Users' ).'" AS section, "2" AS browsernav'
. ' FROM ' . $db->nameQuote('#__community_users') . ' AS c'
. ' INNER JOIN ' . $db->nameQuote('#__users') . ' AS j ON j.id = c.userid'
. ' INNER JOIN ' . $db->nameQuote('jos_community_fields_values') . ' AS s ON j.id = s.user_id'           
. ' WHERE '
. ' MATCH(s.`value`) AGAINST(\'' . $oqu_text . '\')'
. ' AND j.block = ' . $db->quote(0)
. $usersFilter . ' GROUP BY j.id)'
. ' ORDER BY title';

At this point, you can search with any full-text search query in the general Joomla search.

3To add a nice touch, you can install an Ajax Search Dialog like rokajaxsearch http://www.rockettheme.com/extensions-joomla/rokajaxsearch

Just to publish the recipe, I've tried to reduce it to the minimum steps. Please, let me know if you have any question.

JomSocial Recipes