2

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.

11
  • Thank you very much for joining JSE and posting a clear question that includes proof of research/toil -- I always upvote "good" questions like these. The #__fields_values table typically contains multiple rows for a given "id" -- this is deliberate. The table allows you to store multiple pieces of data for a single entity (e.g. a user's hometown, bloodtype, favorite comic book hero, etc). You are likely to need to specify an additional filter to access a particular field value or use aggregate functions. To make your question more clear, please offer a db-fiddle and your desired result.
    – mickmackusa
    Jan 12 at 22:14
  • This answer may inspire: joomla.stackexchange.com/a/29134/12352 ...on second thought, I don't think you need a pivot query. It is more likely that you just need to join on field_id AND item_id instead of only joining on item_id. And a LEFT JOIN will be appropriate.
    – mickmackusa
    Jan 12 at 22:24
  • what is a db-fiddle? Jan 15 at 1:05
  • I included two db-fiddle demos in this answer. By building a runnable example of your exported database table(s), volunteers can test their advice without having to see up a test on their own server.
    – mickmackusa
    Jan 15 at 3:15
  • @mickmackusa - thank you for your input and guidance. It seems that subqueries were the way for me to solve the problem so I wound up getting all the field names in one query, then a second query with records for each user, one field per record. Of course, only the filled in fields per-user where returned. I then built an array of one user with ALL the records they had filled in and used that to iterate (in the oputput) printing an empty string where fields aren't filled in and the data that existed. Jan 21 at 15:44

1 Answer 1

0

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.

1
  • @Josef how did you go with this task? It appears that you have abandoned this question. Is your task resolved?
    – mickmackusa
    Feb 26 at 2:15

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.