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 (  => 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!