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

It's better to use what Joomla! suggest us.

Note by putting 'a' as a second parameter will generate #__content AS a

So, you may have something like this:

$query
  ->select('*')
  ->from($db->quoteName('#__content', 'a'))

Answer 3

Try the following:

$query->select($db->quoteName('user_id') .' AS '. $db->quoteName('name') .' AS '. $db->quoteName('User-Name'));

Be sure to use the full query as described here:

http://docs.joomla.org/Selecting_data_using_JDatabase

So yours will look something like this:

$db = JFactory::getDbo();     
$query = $db->getQuery(true);
$query->select($db->quoteName('user_id') .' AS '. $db->quoteName('name') .' AS '. $db->quoteName('User-Name'));
$query->from($db->quoteName('#__tablename'));     
$db->setQuery($query);

$results = $db->loadObjectList();

Please note that I havent tested this query using AS so let me know if it works or not

Answer 4

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`

Answer 5

I've got this to work:

->select($db->quoteName(array('user_id`' . ' AS ' . '`uid', 'name`' . ' AS ' . '`User-Name')))