DEPRECATION WARNING

This documentation is not using the current rendering mechanism and is probably outdated. The extension maintainer should switch to the new system. Details on how to use the rendering mechanism can be found here.

Allowed keywords

The following SQL keywords are allowed:

  • SELECT
  • FROM
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • WHERE
  • GROUP BY
  • ORDER BY
  • LIMIT
  • OFFSET

On top of these keywords, it is possible to use:

  • aliases (using “AS”), see Advanced uses of aliases <behind-the-scenes-advanced-aliases>
  • the “ON” clause inside a JOIN.
  • DISTINCT at the beginning of the list of SELECTed fields. See more about the use of DISTINCT below.
  • ASC, DESC in the ORDER BY part.
  • any function call in the list of SELECTed fields. More details in SQL function calls <queries-functions>.
  • the alternate OFFSET syntax (i.e. you can use LIMIT x,y or LIMIT y OFFSET x ).

The following is not supported:

  • static values in the SELECT part of the query
  • using brackets around field names. Thus don’t write SELECT (title) AS headline FROM tt\_news, but SELECT title AS headline FROM tt\_news

Using DISTINCT

Using the DISTINCT keyword in your queries requires to pay attention to a couple of peculiarities:

  1. It is very likely that the DISTINCT field in your query is the actual primary key in your query. Assuming this, Data Query does not automatically add the “uid” field to this query (if it exists) to avoid messing up the effect of DISTINCT. Thus it is your responsibility to explicitly designate a “uid” in this case, using aliases. Example:

    SELECT DISTINCT name AS uid FROM fe_users

  2. In a more complex scenario using several fields with DISTINCT, you should build a concatenated string with all fields involved to be used as uid. Example:

    SELECT DISTINCT name, username, CONCAT(name, username) AS uid FROM fe_users

Furthermore, please note that using DISTINCT will disable language overlays (because of the limitations discussed in Behind the scenes).