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');
6
  • 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. yesterday

Your Answer

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

Browse other questions tagged or ask your own question.