SQL pack

SQL pack consists of 3 plugins (field, live value, restriction), they can be found among available options under Processing section -> SQL query.

Use As Field

It is manly usable to display some value in either content/blog view of Form&Content type or list/item view of Search&List type. On the form or search form it will display as ordinary text field and query won't be executed.

Example usage: display average rating for this article when individual ratings are stored in #__ratings table in rating column and parent_id column relates that table to seblod item. 

SELECT ROUND(AVG(rating),2) FROM #__ratings WHERE parent_id = [pk]

Another example, you can use this query to display translated "written by" text together with authors name in the list view of Search&List or in content view of Form&Content: 

SELECT CONCAT('J(Written by) ', name) from #__users as u RIGHT JOIN #__content as c ON u.id = c.created_by WHERE c.id=[pk]

Use As Live Value

You can use it to create a live value, e.g. to store author name together with the item you would add author_name text field and use live value with query like this on it: 

SELECT name from #__users where id=$user->id

Use As Restriction

When query returns a result field will be shown, when there is no result it will be hidden. Using Reverse option reverses this logic, so field will be shown when there is no result, e.g. 

SELECT id FROM #__blue_team_members WHERE id=$user->id
Variables that you can use when constructing query
 
  • $cck->get('fieldname')->someProperty
    This way you can get properties from other fields in the same content type e.g. value, you can also use alternative syntax $cck->getValue('fieldname'). This ONLY works when SQL "as field" is used in the content or list view. See last 2 articles on  http://www.seblod.com/resources/manuals/designer/overriding-a-position for details. *
  • $uri->get('someUrlValue')
    Value of the URL parameter e.g. id, Itemid, catid etc.You can also use any fo the JInput shortucuts to fitlered get (getInt, getString etc. - see https://api.joomla.org/cms-3/classes/JInput.html )
  • $user->someProperty
    Current user's object properties, e.g. id, name, username etc. Also there is $user->getAuthorisedViewLevels()
  • [id] and [pk]
    This are current items id and primary key, e.g. when you are using bridge mode for user creation this will be users id (article has different id). *
  • J(STRING)
    Translate strings using this syntax, in query you can use this to e.g. CONCAT some translated string with query result *

* does not work in Live Value plugin

Note: this extension is available on SEBLOD Store