How to collect multi-row data from #__fields_values and assign specific aliases for each value in the result set?

Question

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 for user 48:

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

In the above table:

• the door value is 5
• the staircase value is C
• the floor value is 2

My problem is that my output looks something like this:

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

but what I need is to have the floor value, not the staircase value.

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?

Answer 1

Raw Pivot Query

SELECT ju.username,
       ju.name,
       MAX(IF(jfv.field_id = 2, jfv.value, NULL)) AS `door`,
       MAX(IF(jfv.field_id = 3, jfv.value, NULL)) AS `staircase`,
       MAX(IF(jfv.field_id = 4, jfv.value, NULL)) AS `floor`
FROM `#__users` AS ju
INNER JOIN `#__fields_values` AS jfv ON ju.id = jfv.item_id
GROUP BY ju.username ASC, ju.name
HAVING MAX(IF(jfv.field_id = 3, jfv.value, NULL)) LIKE 'C%'

Possible Result Set:

| username | name  | door | staircase | floor |
| -------- | ----- | ---- | --------- | ----- |
| FollaKY  | Folla | 5    | C         | 2     |

---

View Demo on DB Fiddle

PHP/Joomla Code (untested tested):

$db = JFactory::getDbo();
$juUsername = $db->qn("ju.username");   // Cache this (D.R.Y.)
$juName     = $db->qn("ju.name");       // Cache this (D.R.Y.)
$jfvFieldId = $db->qn("jfv.field_id");  // Cache this (D.R.Y.)
$jfvValue   = $db->qn("jfv.value");     // Cache this (D.R.Y.)

$query = $db->getQuery(true)
    ->select([
        $juUsername,
        $juName,
        "MAX(IF($jfvFieldId = 2, $jfvValue, NULL)) AS " . $db->qn('door'),
        "MAX(IF($jfvFieldId = 3, $jfvValue, NULL)) AS " . $db->qn('staircase'),
        "MAX(IF($jfvFieldId = 4, $jfvValue, NULL)) AS " . $db->qn('floor')
    ])
    ->from($db->qn('#__users', 'ju'))
    ->innerJoin($db->qn('#__fields_values', 'jfv') . ' ON ' . $db->qn('ju.id') . ' = ' . $db->qn('jfv.item_id'))
    ->group([
        "$juUsername ASC",  // declare the sorting order here
        $juName
    ])
    ->having("MAX(IF($jfvFieldId = 3, $jfvValue, NULL) LIKE " . $db->q("C%"));

// echo $query->dump();  // uncomment if you want to confirm the rendered query
try {
    $db->setQuery($query);
    echo "<pre>";
    var_export($db->loadObjectList());
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // never show getMessage() to public
}

Explanation in steps:

1. Join the users table to the fields_values table. This is a one-to-many relationship for each users row. Using INNER JOIN is different from LEFT JOIN because INNER JOIN will omit users rows that do not have at least one fields_values row to relate to.

2. To prevent multiple rows for the same user, GROUP BY is implemented. This creates "aggregate data" (in other words, a cloud|mass|cluster|pile of fields_values data specific to that user) for each user.

3. To determine which users qualify for the result set based on a row-specific value in fields_values, you will need to perform a comparison on the aggregate data. The HAVING clause will check all of the aggregate data. If an aggregate row does not have a field_id of 3 then it is assigned a value of NULL (within the scope of this sub-process). If the field_id is 3, then the original value is maintained. This creates (unless you have a row with field_id of 3 and a value of NULL -- in which case every value is NULL) a lone non-NULL value to be "picked up" by MAX() -- apply your comparison logic on this lone value.

4. Now that all of the joining, grouping, and filter are finished, it is time to fix up the actual result set. At this point, there is still "aggregate data" being sent to the SELECT clause, but the result set cannot be delivered with "aggregate data" in it (in other words, the rows need to be flattened). Using the same filter&max technique as in the HAVING clause, manually write each specific column that you wish to generate for each row in the result set -- assign whatever column alias you wish after AS. Done.

---

Now there are a few ways to code this up...

• "D.R.Y." (found as comments in my script) stands for "Don't Repeat Yourself". This is a best practices technique which helps to keep your script lean and clean and it spares php having to perform the same task multiple times.
• Sometimes you will see CASE-WHEN statements instead of IF statements inside of MAX() these are logically interchangeable.
• Result set sorting can be done inside of the GROUP BY clause in this scenario, but can also be done with an ORDER BY clause.
• NONE of the $db->qn() calls are actually necessary for this query because none of the table or column names have monkeywrenching characters or are on the list of MySQL Reserved Keywords. As you can see, all of those concatenations and quoting calls reeeeeeally bloat the syntax and make reading and finding typos much harder. If this was my project, I'd be inclined to remove all of the backtick-generating calls and I would also remove quoting calls on static strings like C% ...but this is all a matter of personal preference.