Joomla 3: selecting multiple custom user fields into one html table

Question

Forgive me - pretty much a Joomla/SQL rookie and trying to navigate through some tasks I trying to help my local home owners association with. I'm looking for a way to build my own user list containing multiple custom user fields. My current query gives me multiple records for each user because each user is linked to multiple fields in the "__fields_values" table. I need only ONE record for each user and each custom field as a seperate value in this row. A simple group clause doesn't seem to cut it. Nevermind the actual table html, that's just for testing right now. Hope someone can help me see the light :)

Now I get:

username1|field1|<blank>|email
username1|field2|<blank>|email

My table should look like:

username1|field1|field2|email

My current query and output:

$query = $db->getQuery(true);
$query
->select('*')
->from($db->quoteName('#__users', 'u'))
->join('INNER', $db->quoteName('#__user_usergroup_map', 'm') . ' ON (' . $db->quoteName('u.id') . ' = ' . $db->quoteName('m.user_id') . ')')
->join('INNER', $db->quoteName('#__fields_values', 'f') . ' ON (' . $db->quoteName('u.id') . ' = ' . $db->quoteName('f.item_id') . ')')

->where($db->quoteName('group_id') . ' = ' . $group_id)
->order($db->quoteName('u.username') . ' ASC')

$db->setQuery($query);
$users  = $db->loadObjectList();

And then output to this table:

<table style="width:100%">
<tr style="border-bottom:1pt solid black;text-align: left;">
<th>Name</th>
<th>CustomField1</th>
<th>CustomField2</th>
<th>Email</th>
</tr>
<?php
foreach($users AS $user)
{
?>
<tr>
<td><?php;
 echo $user->name;
?></td>
</td>
<td><?php;
 echo $user->value;
 ?></td>
<td><?php;
 echo $user->NEED ANOTHER VALUE HERE;
 ?></td>
<td><?php;
 echo $user->email;
}?>
</table>

If you're looking to do it in sql, look up SQL pivot (https://stackoverflow.com/q/7674786/1543677 for example). But it's better if you do 2 separate queries, one for users, and one for the extra fields, and merge the arrays in php. — pkExec, Sep 16 '18 at 09:44
Thanks. I have been looking into your suggestion but fail to see how that would allow me to get values from different records presented in the same row. If you have time to explain a bit further that would be highly appreciated. — Peter Jensen, Sep 16 '18 at 15:05
I strongly recommend trying to build a single query for this task. Also, I like to see Joomla questions posted at Joomla Stack Exchange. Your question is nearly complete/perfect. So that I can develop and test my suggested solution, please serve up an sqlfiddle demo of your tables and enough sample data to replicate the situation. I am sure that I could working something up for you, show you some best practices, and explain how it works. — mickmackusa, Oct 04 '18 at 04:37

score 0 · Answer 1 · answered Sep 17 '18 at 05:44

Probably You can do something like this to concatenate the result. If you are not getting the exact result you can just play with the code for what result you want. Your fields will be in the field array.

I have used CONCAT and GROUP_CONCAT. And you can have a look at it here https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

$query = $db->getQuery(true)
            ->select(
                array(
                    '*',
                    "CONCAT('[',GROUP_CONCAT(".$db->quoteName('f.value')."),']') field"
                )
            )
            ->from($db->quoteName('#__users', 'u'))
            ->leftJoin($db->quoteName('#__user_usergroup_map', 'm') . ' ON (' . $db->quoteName('u.id') . ' = ' . $db->quoteName('m.user_id'). ')')
            ->leftJoin($db->quoteName('#__fields_values', 'f') . ' ON (' . $db->quoteName('u.id') . ' = ' . $db->quoteName('f.item_id') . ')')
            ->where($db->quoteName('group_id') . ' = ' . $group_id);

$db->setQuery($query);
$users  = $db->loadObjectList();

This will result in a singly array which you can do a var_dump() and check for yourself.

This workaround is vulnerable to malformed syntax while piecing together a json string representing a 1-dim array. I generally advise against this solution and urge researchers to find a cleaner and more reliable way. — mickmackusa, Oct 04 '18 at 04:43

Joomla 3: selecting multiple custom user fields into one html table

1 Answers1