Search Query

This plugin allows you to construct an SQL query in your search form so that you can retrieve a list of items from any table in your database. Please note that this plugin requires a working knowledge of how to construct an SQL query. While we have provided some worked examples below, please be confident in your ability to construct SQL queries before purchasing this plugin. There are resources for learning SQL available online at W3Schools.

View the tutorial "Using the SEBLOD Search Query Field Plugin" to see how to use this plugin.

SQL QUERY EXAMPLES

Example 1 - return items from 1 table, where the Search Query field is the only filter in your Search Form. In this example we are specifically returning a list of Joomla menu items. The "AND access IN ($user->getAuthorisedViewLevels())" line is ensuring that only the menu items that the user is authorised to view, based on their Joomla Access Level, are returned.

SELECT title, id, level, link, menutype, note, params, type FROM #__menu
WHERE type IN ("alias","component","url")
AND published = 1
AND access IN ($user->getAuthorisedViewLevels())
AND menutype = "mainmenu"
ORDER BY menutype ASC, lft ASC


Example 2 - return a list of items from 1 table, where you have the Search Query field and other filters in your Search Form. In this example we letting the user enter some key words (using a SEBLOD field), and select a menu (also using a SEBLOD field), and then returning all the Joomla menu items that meet all of the criteria.

SELECT title, id, level, link, menutype, note, params, type FROM #__menu
WHERE type IN ("alias","component","url")
AND published = 1
AND access IN ($user->getAuthorisedViewLevels())
AND [MATCH]title||$uri->getValue('seb_keywords')[/MATCH]
AND [MATCH]menutype||$uri->getValue('seb_menu')[/MATCH]
ORDER BY menutype ASC, lft ASC

Example 3 - return items from 2 different tables. In this example we are returning Kuena Forum posts and some Joomla article content.

SELECT a.id AS my_id, a.title AS my_title, a.alias AS my_alias,
b.long_desc AS my_snippet, c.title AS my_category, "Content" AS my_type
FROM #__content AS a
LEFT JOIN #__cck_store_item_content AS b ON b.id = a.id
LEFT JOIN #__categories AS c ON c.id = a.catid
WHERE a.state = 1
AND a.access IN ($user->getAuthorisedViewLevels())
AND [MATCH]b.long_desc||$uri->getValue('seb_generic_search_keyword')[/MATCH]
UNION ALL
SELECT a.thread AS my_id, a.subject AS my_title, c.alias AS my_alias,
b.message AS my_snippet, c.name AS my_category, "Forum" AS my_type
FROM #__kunena_messages AS a
LEFT JOIN #__kunena_messages_text AS b ON b.mesid = a.id
LEFT JOIN #__kunena_categories AS c ON c.id = a.catid
WHERE a.hold = 0
AND [MATCH]b.message||$uri->getValue('seb_generic_search_keyword')[/MATCH]
ORDER BY my_id DESC