This tutorial shows you how to set up the Search Query Plugin available on the SEBLOD Store.
There are 2 steps to using this plugin. The first is to use the Search Query field to retrieve your items from your database. The second step is to display the items you have retrieved in your List or Item view.
Step 1 - using the Search Query field to query the database, and retrieve your required items.
1) Create a new Search Form and make sure the "items that you are searching for" is set to "none". For this example we will use the Table template to display our information
2) Create a new SEBLOD Field, and select the Search Query field as your field type.
3) In the SQL query field, enter your SQL query. Example 1 from below is used in this instance, so that a list of Joomla Menu Items will be retrieved. Note that the storage of this field is "None", since the field is only used to query the database, not store information.
4) Add the SEBLOD Core field called "CCK Storage Location" to your search form, and set the live value to "free"
Set both of your Search Fields to "Hidden"
That completes the first step. You now have a query that is retrieving all of the menu items in your "mainmenu" menu from the database. Now, we need to set up some fields that can display this information in our table.
Step 2 - displaying the items you have retrieved in Part 1 in your List or Item view.
To display your information in your List or Item view, you will need to set up some SEBLOD fields where the items returned in the query can be mapped. To do this, we will create some new text fields where the storage is set to columns of our database that are used in our Search Query.
- Go to your List View
- Create a new SEBLOD text field
- In the Storage options, select Standard > Free
- In the Paramaters field that appears, Select your desired table (this is the table you used in your query) - for this example, this is the #__menu table.
- Then, click the blue « and select your table column. We will start with the "title" column
- Click Save and New
- Repeat steps 2 - 6 for all of the items you are retrieving in your query. For this example this is the id, level, link, menutype, note, type and params.
You have now mapped all of your query items to SEBLOD fields, and displayed them in your table. Now, if you create a menu item link to your List & Search, you will see a table displaying the title, id, level, link, menutype, note and params, of all of the menu items from your mainmenu in a table.
To link results to some component other than com_content you will need Custom link plugin, then you can create a link to your form posts like this:
a) create field named 'id_field' that will contain id value necessary to create correct link. Use storage like with other display fields above, . Put this field to the Hidden position.
b) Put Custom link on the desired field, e.g. Title and click + , your link should look like this: com_something&view=forum&id=$cck->getValue('id_field'). Ofcourse you need to replace link with actual link needed to display desired view, e.g. to show administrator edit view of a menu this would be
administrator/index.php?option=com_menus&view=item&layout=edit&id=$cck->getValue('id_field')
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 are letting the user enter some key words (using a SEBLOD field) which will define the title that all the search results must contain. Similarly, the user can select a menu, and only results that match that menu selection will be returned. These two fields will inherit the match setting applied to your Search Query field.
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
Variables that can be used in queries include: any property of the $user object plus getAuthorisedViewLevels, almost any function of the $uri object and the $cck object.
Combining search query with search values and/or query constructed by Seblod
You can either:
a) use [MATCH] syntax like
[MATCH]database_field_name||$uri->getValue('search_field_name')[/MATCH]
e.g.
[MATCH]title||$uri->getValue('seb_keywords')[/MATCH]
This way query will use your set matching for this field or you can use just $uri->getValue('search_field_name') and hardcode matching e.g.
WHERE some_db_field=$uri->getValue('search_field_name')
both will just alter your custom query based on search fields values
b) use append mode, here your query will be appended to the existing query constructed by seblod search, you can append query parts to the SELECT, GROUP and HAVING part of the query