How to join custom fields data to users table without extra rows in result set

Question

I have rudimentary knowledge of SQL (when it comes to anything but single tables) and am trying to come up with a query that will give me all the custom fields for my users.

This query will give me all my users sorted by group:

$query = $db->getQuery(true)
   ->select('name, email, id')
   ->from($db->quoteName('#__users', u))
   ->join('LEFT', $db->quoteName('#__user_usergroup_map', map) . ' ON ' . $db->quoteName('map.user_id') . ' = ' . $db->quoteName('u.id'))
   ->where('map.group_id' . ' = ' . $group)
   ->order('`name` ASC');

which produces:

SELECT name, email, id
FROM `#__users` AS `u`
LEFT JOIN `#__user_usergroup_map` AS `map` ON `map`.`user_id` = `u`.`id`
WHERE map.group_id = 10
ORDER BY `name` ASC 

but I'm puzzled how to add the custom fields that are in the user context (com_users.user) and pertain to each user (based on id). Joins are something I'm just not familiar with in practice. And, Joomla's shorthand for SQL is something else I'm still puzzling through.

While I wouldn't mind just getting the pieces that would make this work, I'm also interested in the how and why - unless that's way too much for a single question.

UPDATE: having tried a few things, I find I can get all the values in the fields, but then I get repeated records - for each user, I get all the user info plus ONE field - repeated for as many fields per user as there are. What I'm wanting is ONE user with all the field data (hopefully with field names) so I can present that on a page. Here's what I tried:

$query = $db->getQuery(true)
    ->select('u.name, u.email, u.id, fv.value')
    ->from($db->quoteName('#__users', u))
    ->join('LEFT', $db->quoteName('#__user_usergroup_map', map) . ' ON ' . $db->quoteName('map.user_id') . ' = ' . $db->quoteName('u.id'))
    ->join('RIGHT', $db->quoteName('#__fields_values', fv) . ' ON ' . $db->quoteName('fv.item_id') . ' = ' . $db->quoteName('u.id'))
    ->where('map.group_id' . ' = ' . $group)
    ->order('`name` ASC');

---

Update: 1/24 - @mickmackusa - thanks for pushing me on this.

What I did was execute a query on all the fields so I had the names. Then, I executed a query to get all the records (one field per record). Then I combined all the records into one array and passed the fields and records to the tmpl/default.php script.

Here's the queries and joining the records:

// let's try this: get all the field value names per category
$query = $db->getQuery(true);
$query
        ->select('fields.title')
        ->from($db->quoteName('#__fields', fields))
        ;
$db->setQuery($query, 0, 0);
$fv_list = $db->loadObjectList();
//array_unshift($fv_list, "name", "email");
//print_r($fv_list);

// now get all the fields_values
$q1 = $db->getQuery(true);
$q1
        ->select('u.name, u.email, f.title, fv.value')
        ->from($db->quoteName('#__users', u))
        ->join('INNER', $db->quoteName('#__user_usergroup_map', map) . ' ON ' . $db->quoteName('map.user_id') . ' = ' . $db->quoteName('u.id'))
        ->join('INNER', $db->quoteName('#__fields_values', fv) . ' ON ' . $db->quoteName('fv.item_id' ) . ' = ' . $db->quoteName('u.id'))
        ->join('INNER', $db->quoteName('#__fields',        f)  . ' ON ' . $db->quoteName('fv.field_id') . ' = ' . $db->quoteName('f.id'))
        // ->where('map.group_id' . ' = ' . $group)
        ->order('`name` ASC')
        ;
$db->setQuery($q1, 0, 0);
$fields = $db->loadObjectList();
// echo $q1 .EOL . EOL;
//print_r($fields);

foreach ($fields as $fv)
{
        $name = $fv->name;
        $list[$name][0] = $name;
        $list[$name][1] = $fv->email;
        $list[$name][$fv->title] = $fv->value;
}
return (array($fv_list, $list));

This produces an array of two arrays - the first is the list of field names, the second is one record per user with all the fields they've populated as key/value pairs.

In default.php, I output ALL the fields either with data the user populated or "" if the field is empty.

I'm still working on getting the data exported - but the two queries here work on Joomla 4.0.x databases, although I'm not getting group filtering like I want.

Answer 1

To better understand your scenario, I've created this db-fiddle with your sample data and made a basic SQL query to display the JOINed data as a tabular result set.

SELECT u.id, u.name, u.email, ugm.group_id, .f.title, fv.value
FROM lmnop_users              AS u
JOIN lmnop_user_usergroup_map AS ugm ON u.id = ugm.user_id
JOIN lmnop_fields_values      AS fv  ON u.id = fv.item_id
JOIN lmnop_fields             AS f   ON fv.field_id = f.id
WHERE ugm.group_id = {your group id}

Using LEFT JOINs on the #_fields_values and #__fields tables will be advisable if it is ever possible that you have users with no records in these tables AND you still want to present these users' rows.

For your dynamic pivot, you can pre-fetch all #__fields titles, and create a pivot query on the fly in PHP.

$titleQuery = $db->getQuery(true);
$titleQuery->select('title')->from('#__fields');
$db->setQuery($titleQuery);
$fieldsTitles = $db->loadColumn();

then

$query = $db->getQuery(true);
$query->select('u.id, u.name, u.email');
foreach ($fieldsTitles as $title) {
    $query->select(
        sprintf(
            "COALESCE(MAX(IF(f.title = %s, fv.value, NULL)), '') AS %s,
            $db->q($title),
            $db->qn($title)
        )
    );
}
$query
    ->from($db->qn('#__users', 'u'))
    ->join('INNER', '#__user_usergroup_map m ON m.user_id = u.id')
    ->join('INNER', '#__fields_values     fv ON fv.item_id = u.id')
    ->join('INNER', '#__fields             f ON fv.field_id = f.id')
    ->where('m.group_id = ' . (int)$group)
    ->group('u.id, u.name, u.email')
    ->order('u.name ASC');
$db->setQuery($query);
return $db->loadObjectList();

This should group, flatten, and coalesce all missing values to a default empty string. Despite not actually testing the above code, I expect it to return this result -- https://www.db-fiddle.com/f/nrDG5StEdHpKjhyRDHedRA/1

---

If it is possible that your project will have rows in the #__fields_values table that doesn't relate to your users, then you'll need to filter the title fetching query. Alternatively, there is nothing wrong with grabbing my very first (non-pivot) query, extracting all of the unique title values from the verbose result set, then iterating your result set to manually group and flatten your results and pushing default values into every new user encountered using the unique titles array.

p.s. In your PHP code, remember to wrap your table alias string in quotes so that your PHP code compiles.

Answer 2

I found this link: https://ubiq.co/database-blog/transpose-rows-columns-dynamically-mysql/ which helped me to create a pure MySQL solution:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(
      'max(case when `f`.`name` = ''',
      `f`.`name`,
      ''' then `fv`.`value` end) `',
      `f`.`name`,
      '`'
  ) INTO @sql
FROM `#__fields` `f` 
JOIN `#__fields_groups` `fg` ON `f`.`group_id`=`fg`.`id`
WHERE `f`.`context`='com_users.user' ORDER BY `fg`.`ordering`,`f`.`ordering`;

SET @sql = CONCAT('SELECT `u`.*, ', @sql, ' 
FROM `#__users` `u`
JOIN `#__fields_values` `fv` ON `u`.`id`=`fv`.`item_id`
JOIN `#__fields` `f` ON `fv`.`field_id`=`f`.`id`
GROUP BY `u`.`id` ORDER BY `u`.`name`');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;