mysqljoomlaaliasbackticks

How to assign aliases to an array of column names with Joomla's quoteName()?

I want to use the AS statement for Aliases in a query. I use this piece of code:

$query->select($db->quoteName(array('NameInQ as nin', 'Name')));

Anyway I get this error:

'Unknown column 'NameInQ as nin' in 'field list'

NameInQ does exist as a column name in the table. nin should be the alias.

What am I doing wrong?

Solution

  • When you tell Joomla:

    $query->select($db->quoteName(array('NameInQ as nin', 'Name')));

    echo $query->dump(); will tell you:

    SELECT `NameInQ as nin`,`Name`

    See how it doesn't know how to differentiate an aliased column name from a string with spaces in it?

    The Docs: https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_quoteName

    If you want to assign aliases to column names in Joomla from within the qn() / quoteName() method, you will need to nominate corresponding aliases for all columns.

    $query->select($db->quoteName(array('NameInQ', 'Name'), array('nin', 'Name')));

    Renders as:

    SELECT `NameInQ` AS `nin`,`Name` AS `Name`
//     ^-------^----^---^-^----^----^----^-- everything aliased, everything backtick wrapped

    Or, of course you could individualize the quoteName() calls, you can avoid aliasing every column.

    $query->select(array($db->quoteName('NameInQ', 'nin'), $db->quoteName('Name')));

    Renders as:

    SELECT `NameInQ` AS `nin`,`Name`

    Finally, the truth is: You don't even need to quote any of your sample column names because the query will be stable/secure without the extra method call(s). *I recommend leaving them out to minimize query bloat and developer eye-strain.

    $query->select(array('NameInQ AS nin', 'Name'));

    or even in raw form:

    $query->select('NameInQ AS nin, Name');

    For the record, Name (MYSQL is case-insensitive) IS a KEYWORD, but it is not a RESERVED KEYWORD.

    See the MySQL Doc: https://dev.mysql.com/doc/refman/5.5/en/keywords.html#keywords-5-5-detailed-N (there is no "(R)" beside Name