How to assign different aliases based on values

by FollaKY   Last Updated October 16, 2019 15:10 PM

I've been searching for an answer to this question everywhere. Perhaps I was not asking the right question :-D

In Joomla I have the following (working) query to get name, username and the value of a custom field:

// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

// Select name and username from USERS table and value from FIELDS_VALUES table.
// Define USERS table as ju
// Define FIELD_VALUES as jfv and match id and item_id to perform an inner join
// Set condition
// Set Order as ascending

$query
    ->select(array('ju.username', 'ju.name', 'jfv.value'))
    ->from($db->quoteName('#__users', 'ju'))
    ->join('INNER', $db->quoteName('#__fields_values', 'jfv') . ' ON (' . $db->quoteName('ju.id') . ' = ' . $db->quoteName('jfv.item_id') . ')')
    ->where($db->quoteName('jfv.value') . ' LIKE ' . $db->quote('C%'))
    ->order($db->quoteName('ju.username') . ' ASC');

// Reset the query using our newly populated query object.
$db->setQuery($query);

// Load results as a list of objects in an array
$results = $db->loadObjectList();
print_r($results);

This is used to display the name and username of a user together with door, floor and staircase in an apartment building. In this case all people from staircase C.

The table #__fields_values looks like this

field_id | item_id | value |
============================
2        | 48      | 5     |
============================
3        | 48      | C     |
============================
4        | 48      | 2     |
============================

where 5 is the door, C is the staircase and 2 is the floor.

So far, so good. My problem, if we want to call it like that, is that my output looks something like this:

Array ( [0] => stdClass Object ( [username] => C1 [name] => NameOfTheGuy [value] => C )

but what I'd rather need would be to have the floor, not the staircase.

Is it possible to assign an alias based on the value inside a column (let's say jvf.staircase if jfv.field_id = 3)? Or is there a better way to achieve what I want to do?

Thanks in advance for your answers, your help is really appreciated!

Tags : sql alias


Related Questions


Updated March 14, 2017 10:10 AM

Updated April 11, 2016 08:04 AM

Updated April 25, 2016 08:04 AM

Updated November 13, 2016 08:04 AM

Updated May 08, 2017 15:10 PM