Joomla database query SELECT AS

Question

So I'm just hypothetically thrilled to be querying my hypothetical database:

$query->select($db->quoteName(array('user_id', 'name')));

I would, however, like the query to look like:

SELECT `user_id` AS `uid`, `name` AS `User-Name`

How the heck do I get the AS in there?

Answer 1

I know this question is 6 months old, so you've probably found an answer or worked around it, but for anyone else who has a similar problem:

$query->select($db->quoteName(array('user_id','name'),array('uid','User-Name')));

If you only want to use an alias for some fields, just pass null in the array for the fields you don't want to alias, so for example:

$query->select($db->quoteName(array('user_id','name'),array(null,'User-Name')));

would give

"SELECT `user_id`, `name` AS `User-Name`"

Answer 2

My preferred way is this:

I create an array with the fields I want to select:

$fields = array(
    'a.id'     => 'id',
    'a.field1' => 'field1',
    'a.field2' => 'field2',
    'a.field3' => 'field3',
    'b.field1' => 'bfield1',
    'b.field2' =>  null,
    'b.field3' => 'bfield3',
);

In the above array, the keys are used for the db Column names of the query, the values for the aliases, as you can see later in the $query->select().
*When you do not need an alias - just set null.

This helps better to control and check what fields I want and how to name them - and is better for maintenance or changes and is portable enough, as I only have to change my $fields array according to my needs.

Then the Joomla select command can be like:

$query->select( $db->quoteName(
    array_keys($fields),
    array_values($fields)
));

This will produce the following SQL SELECT query:

SELECT `a`.`id` AS `id`,`a`.`field1` AS `field1`,`a`.`field2` AS `field2`,`a`.`field3` AS `field3`,`b`.`field1` AS `bfield1`, `field2`, `b`.`field3` AS `bfield3`