SQL queries

Parent Previous Next

This page is used to manage (add, edit or delete) data sources which select data from databases. Data are selected based on a SQL query (usually it is SELECT query).


On this page administrator can

  1. Create a new data source using "New" button.
  2. Edit an existing data source by clicking it's name or by selecting checkbox at left of appropriate data source and clicking "Edit" button.
  3. Remove data sources by selecting checkbox at left of appropriate data sources and clicking "Delete" button.


Create/edit data source

The following parameters can be defined for data source:

SELECT id, title FROM #__content

More information about syntax of SELECT SQL query is available here. Different databases are also used different dialects of SQL language. More information can be found in documentation for the database which is used on your server.

It is possible to use the following predefined variables in WHERE clause:

For example if want to select id and title of articles which are created by currently logged user, use the following query:

SELECT id, title FROM #__content WHERE created_by = {$UserId}

It also possible to use request variables in WHERE clause. They are used in the following way:

       {$REQUEST:VAR_NAME}

Where VAR_NAME is a name of request variable. For example if want to show id and title of articles where title contains value of search request variable, use the following SQL query:

SELECT id, title FROM #__content WHERE ({$REQUEST:search|empty:''} = '' OR title LIKE CONCAT('%', {$REQUEST:search|empty:''}, '%'))


The following parameters are used to defined some properties and formatters for fields in the selected data:

<img src="{$value}" />

If the column contains links to pages and want to convert them to HTML hyperlinks, use the following value:

<a href="{$value}" target="_blank">Open page</a>

It is also possible to use values from other columns from the same record. For this user {$columns:COLUMNNAME} variable where COLUMNNAME is name of the column. For example you use SELECT ProductId,ProductName,ProductDetailsLink FROM products query to select data and want to convert values of ProductDetailsLink column to hyperlinks and use value of ProductName column as link text. For this set "Columns settings → ID" parameter to ProductDetailsLink value and "Columns settings → Format" parameter to the following value:

<a href="{$value}">{$columns:ProductName}</a>


"Data transformation" section is used to transform data. If "Revert data" parameter is set to "Yes", the selected data will be reverted. For example the following data:

#

col 1

col 2

col 3

val 1

val 1-1

val 1-2

val 1-3

val 2

val 2-1

val 2-2

val 2-3

will be transformed in following way:

#

val 1

val 2

col 1

val 1-1

val 2-1

col 2

val 1-2

val 2-2

col 3

val 1-3

val 2-3

This transformation can be useful if necessary to prepare data for a graph or a chart.


"Graph transformation" section is also used to transform data. It accepts the following parameters:


To understand how it works, see how the following table will be transformed:

SubmissionId

Name

Value

1

FirstName

Tom

1

LastName

Smith

1

Age

33

2

FirstName

John

2

LastName

Doe

2

Age

21

If X axis column parameter is set to Name value, Y axis column is set to SubmissionId value and Value column is set to Value then the data will be transformed in the following way:

FirstName

LastName

Age

1

Tom

Smith

33

2

John

Doe

21

If "Revert data" and "Graph transformation" are enabled together then "Revert data" will be applied first and after this "Graph transformation" will be applied. If any transformation is used, applications can't retrieve data dynamically (via AJAX for example).