DPQL_ALIAS

List of functions

DPQL_ALIAS performs the same function as the 'AS' i.e. it renames expressions to something more readable.

When writing complex queries though it can be easier to use as it's perhaps more obvious where it should be placed.

The example query below will generate a matrix table showing number of tickets created grouped by agents and date created.

SELECT DPQL_COUNT()
FROM tickets
GROUP BY DPQL_MATRIX(tickets.agent, DATE_FORMAT(tickets.date_created, '%Y-%M'))

However we've used the DATE_FORMAT function to format the date so they are grouped by month and year.

This works but as you can see in the image below the date column shows the whole query for its title.

matrix-1.png

What we can do though is amend this with an alias to something more sensible:

SELECT DPQL_COUNT()
FROM tickets
GROUP BY DPQL_MATRIX(tickets.agent, DPQL_ALIAS(DATE_FORMAT(tickets.date_created, '%Y-%M'), 'Date Created'))

Which would render as:

matrix-2.png

It's simply a case of adding the ALIAS function before the element you want to rename and enclosing the brackets so it should be easy to place even in particularly complex queries.

Published: 05/07/2019

Last updated: 05/08/2019