Difference between revisions of "Using Geeklog's Improved Search Engine"

From GeeklogWiki
Jump to: navigation, search
m (Returning Multiple Objects: code typo)
m (Standard SQL Query)
Line 83: Line 83:
  
 
All SQL queries returned should be without the LIMIT and ORDER BY clauses. The SQL query must have the following column names and look like:
 
All SQL queries returned should be without the LIMIT and ORDER BY clauses. The SQL query must have the following column names and look like:
<pre>SELECT id, title, description, date, uid, hits, url FROM ... WHERE ...{space}</pre>
+
<pre>SELECT id, title, description, date, uid, hits, url FROM ... WHERE ...</pre>
 
If a column does not exist in the table then another value should be substituted, for example: '0' AS hits, ...
 
If a column does not exist in the table then another value should be substituted, for example: '0' AS hits, ...
  

Revision as of 21:14, 30 September 2009

Plugins' compatibility with old Geeklog versions

If you want to make your plugin compatible with Geeklog v1.5 and below, you must also implement Using Geeklog's Search Engine.

The functions

Tying your plugin into Geeklog's search API is rather easy. Easy, that is, if you know how to search your plugin's data already. The Geeklog search API provides a way for you to return the SQL query back to Geeklog which will be executed in the core and the results included in its search page.

For the data in your plugin to be searched by Geeklog's search functions, there are two functions that you need to implement in your plugins function.inc:

  • plugin_searchtypes_{plugin_name}() returns to Geeklog the type(s) of search.
  • plugin_dopluginsearch_{plugin_name}() returns the search SQL query back to Geeklog.

Let's look at each of them in turn:

plugin_searchtypes_{plugin_name}()

This function takes no parameters and returns an associative array of the search type. The normal code for this function will make it all clear. Normally the function looks like this:

function plugin_searchtypes_{plugin_name}()
{
    global $LANG_PL00;
    $tmp['{plugin_name}']= $LANG_PL00['searchtype'];
    return $tmp;
}

(don't forget to replace {plugin_name} with the actual name).

Naturally all occurrences of plugin would be replaced with the name of your plugin and the LANGUAGE variable $LANG_PL00 is just an example. Your plugin needs to use a unique variable name so replace PL00.

The return array is of the format array['searchtype'] = 'Search Description', where searchtype is returned to your plugin search routine and Description is displayed in the Search Page Drop Down box.

You can have multiple search types and descriptions.

plugin_dopluginsearch_{plugin_name}()

This section will outline the method used to search a plugin's data.

Input Parameters

The parameters that are passed to this function are as follows:

  • $query -- a string containing the search term.
  • $datestart -- starting date to begin search.
  • $dateend -- ending date to end search.
  • $topic -- topic item is assigned to.
  • $type -- no longer used, deprecated.
  • $author -- the user id of the author.
  • $keyType -- search key type: 'all', 'phrase', 'any'.
  • $page -- no longer used, deprecated.
  • $perpage -- no longer used, deprecated.

Depending on your plugin, some of these criteria may be meaningless and thus ignored. The function should then perform two basic operations:

  1. Build search SQL string using the input parameters.
  2. Return the query along with some plugin information.

Returned Values

The search API requires the following information to be returned by the plugin_dopluginsearch_{plugin_name}() function:

Plugin Name
  • A name to display to the user, i.e. 'My Plugin' (singular preferred)
  • A name used locally to identify the plugin, i.e. 'myplugin'
A Search Rank
  • An integer between 1 and 5 based on how many result to extract from the query.

As all the results have been combined into a single list, the core needs to prioritize important plugins to non important ones. The higher the ranking the more results will be displayed to the user. A rank of 1 will show fewer results, where as a rank of 5 shows the most amount of results from the plugin. The rank is optional, if it's not provided it will default to 3.

Here is an example of Geeklog setup with three plugins, the different rank values will allow varying results from each plugin. The results page is set to display a total of 50 results.

   plugin      rank    results
   ---------------------------
   stories     5       23
   forums      4       18
   comments    2       9
Standard SQL Query
  • A string containing a single query.
  • OR An array of two queries for both MSSQL and MySQL DBMS.

All SQL queries returned should be without the LIMIT and ORDER BY clauses. The SQL query must have the following column names and look like:

SELECT id, title, description, date, uid, hits, url FROM ... WHERE ...

If a column does not exist in the table then another value should be substituted, for example: '0' AS hits, ...

URL

The URL is where to take the user when they have clicked a result. It should start with a single slash if the target is within the current domain. Otherwise the result will be printed, as is, in the href attribute of the link. For example this URL belongs to the Geeklog site:

CONCAT('/staticpages/index.php?page=', sp.sp_id) AS url

However some plugins may need to direct users to an external site, ie the Links plugin. In this instance providing a full URL will suffice.

Full-Text SQL Query (optional)
  • An array of two queries for both MSSQL and MySQL DBMS using the Full-Text search method.

This search method will take advantage of Full-Text indexes which are optimised to reduce search times. This should only be returned if the columns being searched have been properly indexed. The Full-Text SQL Query will only be executed if Full-Text searches have been enabled by the admin during the installation or upgrading process, otherwise the core will always fall back to the Standard SQL Query.

The SearchCriteria() Class

The process of returning the values is done by initializing a SearchCriteria() object, setting the parameters for the search then returning the object. Here is an example:

$search = new SearchCriteria('myplugin', 'My Plugin');
$search->setSQL($sql);
$search->setFTSQL($ftsql);
$search->setRank(4);

This indicates that the 'myplugin' plugin supports Full-Text searches and will have a ranking of 4.

buildSearchSQL() Function

As a lot of the plugins will be processing the same or similar SQL queries the buildSearchSQL() function has been provided to simplify things. This function will take four parameters then build the searching part of the SQL query. It will also return the Full-Text query strings should they be required. The parameters are:

  • $keyType -- search key type: 'all', 'phrase', 'any'.
  • $query -- the query string.
  • $columns -- the column names to search.
  • $sql -- the sql query to append to. (optional)

And an example of how it can be put to use:

$sql = 'SELECT ... FROM ... WHERE ... ';
$columns = array('title','bodytext');
list($sql,$ftsql) = buildSearchSQL('any', 'my geeklog', $columns, $sql);

// $sql => SELECT ... FROM ... WHERE ... AND ((title LIKE '%my%' OR bodytext LIKE '%my%') OR  (title LIKE '%geeklog%' OR bodytext LIKE '%geeklog%'))
// $ftsql[mysql] => SELECT ... FROM ... WHERE ... AND MATCH(title,bodytext) AGAINST ('my geeklog' IN BOOLEAN MODE)
// $ftsql[mssql] => SELECT ... FROM ... WHERE ... AND CONTAINS((title,bodytext), '"my" OR "geeklog"')

A proper example of its use is at the bottom of the page.

Enabling URL Rewrite

If the plugin requires the URL to be passed through the COM_buildUrl() function then it should set the setURLRewrite() function to true. For example:

$search = new SearchCriteria('myplugin', 'My Plugin');
$search->setSQL($sql);
$search->setURLRewrite(true);
Returning Multiple Objects

In some cases a plugin may be required to search across two or more tables that have no relation with one another. To accommodate this they may return an array of SearchCriteria() objects, each with a different SQL query. To allow the user to differentiate between the results an array of names can be passed, each name will be a separate sub group and will be appended to one another using the separator from the configuration.

The following example shows how this works:

// Search the main table
//   These results will be labelled 'My Plugin > Main'
$search_main = new SearchCriteria('myplugin', array('My Plugin', 'Main'));
$search_main->setSQL($sql_main);
$search_main->setFTSQL($ftsql_main);
$search_main->setRank(4);

// Search a sub tables
//   These results will be labelled 'My Plugin > Sub'
$search_sub = new SearchCriteria('myplugin', array('My Plugin', 'Sub'));
$search_sub->setSQL($sql_sub);
$search_sub->setFTSQL($ftsql_sub);
$search_sub->setRank(4);

// Return both objects
return array($search_main, $search_sub);

Note: The plugin identifier needs to stay the same across all objects, in this case 'myplugin'.

Identifying A Plugin's Comments

Some plugins may have comments associated with them such as the FileMgmt or the Polls Plugin. To allow these comments to be searched the core needs to know where to find them. The process is similar to returning multiple SearchCriteria() objects with the exception of identifying the object that contains the comments. This is done by setting the setComment() function to true. As comments will be included in general searches it is best practice to set a lower rank for the comments object to ensure the search results page isn't spammed with comment results.

Below is a short example.

// Search the main table
$sql_main = "SELECT ... FROM gl_myplugin WHERE ...";
$search_main = new SearchCriteria('myplugin', 'My Plugin');
$search_main->setSQL($sql_main);
$search_main->setRank(4);

// Search the comments tables
$sql_comments = "SELECT ... FROM gl_comments" WHERE ...";
$search_comments = new SearchCriteria('myplugin', array('My Plugin', 'Comments'));
$search_comments->setSQL($sql_comments);
$search_comments->setComment(true);
$search_comments->setRank(1);

// Return both objects
return array($search_main, $sql_comments);

Note: The plugin identifier needs to stay the same across all objects, in this case 'myplugin'.

A Working Example

This example searches the Stories. Although it's not a plugin, it puts to use everything discussed here so it's a good example of how to implement the API in your plugin.

function plugin_dopluginsearch_searchStories($query, $datestart, $dateend, $topic, $type, $author, $keyType, $page, $perpage)
{
    global $_TABLES, $_DB_dbms, $LANG09;

    // Make sure the query is SQL safe
    $query = trim(addslashes($query));

    // Build the first part of the query
    $sql = "SELECT
                s.sid AS id,
                s.title AS title,
                s.introtext AS description,
                UNIX_TIMESTAMP(s.date) AS date,
                s.uid AS uid,
                s.hits AS hits,
                CONCAT('/article.php?story=',s.sid) AS url ";
    $sql .= "FROM {$_TABLES['stories']} AS s, {$_TABLES['users']} AS u ";
    $sql .= "WHERE (draft_flag = 0) AND (date <= NOW()) AND (u.uid = s.uid) ";
    $sql .= COM_getPermSQL('AND') . COM_getTopicSQL('AND') . COM_getLangSQL('sid', 'AND') . ' ';

    // If we are searching by date add that too
    if (!empty($datestart) && !empty($dateend)) {
        $delim = substr($datestart, 4, 1);
        if (!empty($delim)) {
            $DS = explode($delim, $datestart);
            $DE = explode($delim, $dateend);
            $startdate = mktime(0,0,0,$DS[1],$DS[2],$DS[0]);
            $enddate = mktime(23,59,59,$DE[1],$DE[2],$DE[0]);
            $sql .= "AND (UNIX_TIMESTAMP(date) BETWEEN '$startdate' AND '$enddate') ";
        }
    }
    if (!empty($topic)) {
        $sql .= "AND (s.tid = '$topic') ";
    }
    if (!empty($author)) {
        $sql .= "AND (s.uid = '$author') ";
    }

    // Create a SearchCriteria instance with the name of the plugin
    $search = new SearchCriteria('stories', $LANG09[65]);

    // These are the columns in the table that need searching
    $columns = array('introtext','bodytext','title');

    // Get back the completed SQL query
    list($sql,$ftsql) = $search->buildSearchSQL($keyType, $query, $columns, $sql);

    // Set the Std. SQL Query
    $search->setSQL($sql);

    // Set the Full-Text Query, remember the columns _MUST_ be indexed first. If they are not then don't set this.
    $search->setFTSQL($ftsql);

    // Finally set a high ranking, enable URLRewrite and return the object.
    $search->setRank(5);
    $search->setURLRewrite(true);
    return $search;
}

For more examples, there's a forum post with updated search functions for the FAQ Manager, Forum, and File Management plugins.