Search Query for SEBLOD

• Construct powerful custom SQL queries
• Return a list of non-SEBLOD items from your database

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 : http://www.seblod.com/resources/tutorials/using-the-search-query-field-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('sebkeywords')[/MATCH]
AND [MATCH]menutype||$uri->getValue('sebmenu')[/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 myid, a.title AS mytitle, a.alias AS myalias,
b.longdesc AS mysnippet, c.title AS mycategory, "Content" AS mytype
FROM #content AS a
LEFT JOIN #cckstoreitemcontent 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.longdesc||$uri->getValue('sebgenericsearchkeyword')[/MATCH]
UNION ALL
SELECT a.thread AS myid, a.subject AS mytitle, c.alias AS myalias,
b.message AS mysnippet, c.name AS mycategory, "Forum" AS mytype
FROM #kunenamessages AS a
LEFT JOIN #kunenamessagestext AS b ON b.mesid = a.id
LEFT JOIN #kunenacategories AS c ON c.id = a.catid
WHERE a.hold = 0
AND [MATCH]b.message||$uri->getValue('sebgenericsearchkeyword')[/MATCH]
ORDER BY myid DESC