Mysqli using AS clause not working

Question

I have two tables that are joined. Both have an id column.

$query
->select($db->quoteName(array(
'a.id',
'a.address_one',
'a.address_three',
'a.address_two',
'b.id AS myStuff',
'b.person_id'
'b.packages_id',
'b.payment_date',

)))

->from($db->quoteName('#__person','a'))
->join('INNER', $db->quoteName('#__licence', 'b') . ' ON (' . $db->quoteName('a.id') . ' = ' . $db->quoteName('b.person_id') . ')')
->where($db->quoteName('a.published') . ' = '. $db->quote('1'))
->where($db->quoteName('b.published') . ' = '. $db->quote('1'));

However the AS clause doesn't work and throws an error.

the returned query has b.id AS myStuff,

rather than

b.id AS myStuff,

I've tried all the combinations I can think of for the clause but no matter what I use I get an error.

Can someone tell me that correct format for using an AS clause in the query?

Update

So I've found that if I remove the b.id AS line and then add a separate

$query->select('b.id AS myStuff');

the query works although the ` marks are missing. Not sure if I'm missing some other way of tackling it all within the original query.

Answer 1

Alias should be passed as second argument to quoteName(). When first argument is an array, second argument should also be an array of the same length. For columns without values you should pass null. Which doesn't look good in your case:

$query
    ->select(
        $db->quoteName(
            array(
                'a.id',
                'a.address_one',
                'a.address_three',
                'a.address_two',
                'b.id',
                'b.person_id',
                'b.packages_id',
                'b.payment_date',
            ),
            array(
                null,
                null,
                null,
                null,
                'myStuff',
                null,
                null,
                null,
            )
        )
    )

You can break out the column with alias as you already mentioned. If you want to keep it quoted (which in reality isn't necessary unless identifiers contain reserved keywords), just wrap it in a separate quoteName() call:

$query->select(
    $db->quoteName(
        array(
            'a.id',
            'a.address_one',
            'a.address_three',
            'a.address_two',
            'b.person_id',
            'b.packages_id',
            'b.payment_date',
        )
    )
)
    ->select($db->quoteName('b.id', 'myStuff'));

Personally I prefer passing an array of individually quoted columns. It's more code to write but also allows mixing columns with/without aliases and expressions inside single select() call. And it's also faster due to how quoteName() currently handles arrays.

$query->select(
    array(
        // Allows column without alias.
        $db->quoteName('a.id'),
        // Allows column with alias.
        $db->quoteName('b.id', 'myStuff'),
        // Allows expressions.
        COUNT(*),
    )
)