Advanced uses of aliases¶

Aliases can be used in the SQL query to modify where the data is stored in the Data Structure (as explained above). It is possible to “move” a field from one table to another. Let’s take a look at the SQL query that appears in the introductory screenshot:

SELECT uid, title, COUNT(children.uid) AS pages.children FROM pages
LEFT JOIN pages AS children ON children.pid = pages.uid
WHERE children.uid IS NOT NULL AND pages.pid = 1
ORDER BY pages.title ASC GROUP BY pages.uid

If you try to execute it as is you will get several SQL errors. It is indeed not correct, but will be by the time Data Query has rewritten it. Anyway the important point here is to look at the alias used for COUNT(children.uid): “pages.children”. What this will do is to “move” the “children” column to the “pages” table.

The result of the above query will be something like:

To Data Query all fields now seem related to the “pages” table. This will result in a Data Structure with no subtables. This is often very convenient as it makes it easier to use the results (in “templatedisplay” for example) and can save unnecessary loops on subtables.