1

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.

1 Answer 1

2

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(*),
    )
)
2
  • Thanks @Sharky All working now. I couldn't find anywhere this is mentioned in the Joomla docs - am I just missing it? Aug 18, 2020 at 14:56
  • There's API documentation api.joomla.org/cms-3/classes/….
    – Sharky
    Aug 19, 2020 at 5:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.