1

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?

  • My intuition tells me that you are seeking a pivot, however after reading your question a few times, I'm not fully following your logic with the sample data. I'd like to see your exact desired result set for your sample data. To make things REALLY easy for volunteers (especially ones that provide support predominately from their mobile phone -- like me), you can provide a db-fiddle.com demo for us to play with / test on. – mickmackusa Oct 16 at 21:40
  • I am happy to post an answer after work once I understand your exact requirements. Here's something that I mocked up on the train... db-fiddle.com/f/ms4n8QxymVdKsXS7fJ9hec/0 If this is getting close to what you want, please let me know and I'll post a proper answer with Joomla's query builder syntax. – mickmackusa Oct 16 at 22:27
  • Yes, that absolutely works and you fully nailed it, even on the train! Is this a pivot as you've been mentioning eralier? I'll remember to post a db-fiddle next time, thanks for the advice. – FollaKY Oct 17 at 6:25
0

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.
  • Many apologies @FollaKY my untested snippet had too many typos. I got a chance to test my updated snippet on my localhost and it's definitely all good now. – mickmackusa Oct 19 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.