by Octopoos

Field Octopoos Paid

Construct an SQL query in your search form so that you can retrieve a list of items from any table in your database

* excl. VAT
  • Construct powerful custom SQL queries
  • Return a list of non-SEBLOD items from your database
  • Joomla! 3.x
  • Joomla! 4.x

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

Business Clubs:

  • Lifetime License
  • Unlimited Domains

Single purchase:

  • Free Updates
  • Lifetime License
  • Unlimited Domains

Details:

1061
15857
GNU GPLv2
2014-10-15
2023-02-09
2023-02-09

Requires:

Latest SEBLOD

Screenshots

Downloads

ChangelogFileVersionDate

! SEBLOD 4 ready
(use 1.6.0 for SEBLOD 3)

Sign In1.7.02023-02-09

# Quick fix.

Sign In1.6.02020-12-13

# Quick fix.
^ Copyright updated.

Sign In1.5.22018-06-27

# Matching Syntax issue fixed.
# "ORDER" only for "append".

Sign In1.5.12017-06-14

+ "ORDER" clause added.

Sign In1.5.02017-05-10

+ [MATCH] syntax improved.
+ [ORDER] syntax added.

Sign In1.4.02016-08-26

^ Copyright updated.
+ "WHERE" clause added.
- Remove useless parameters.

Sign In1.3.02016-05-04

+ "GROUP" clause added.
+ "User Variables" added.
# Restriction issue fixed.

Sign In1.2.02015-05-09

+ Append mode added.
+ SELECT parameter added.
+ HAVING parameter added.

Sign In1.1.02014-12-07

Initiale Release.

Sign In1.0.02014-10-17

Rating & Reviews