I have added a custom fields on the user management, which works greats, the custom fields are save on fields fields_values tables, I don't find a way to join the info from user table users table and the fields tables in one query results.
I need something like: SELECT id,NAME,username,customfield FROM users WHERE Id=2
How can I achieve that?
SELECT u.*, field_id as fid, value as fvalue FROM `#__users` as u LEFT JOIN `#__fields_values` AS f ON u.id = f.item_id WHERE u.id=2
// u as user table
// f as fileds table
// u.id as userid (user table user id)
// f.item_id as userid (fields table user id)
#_ needs Joomla to fill in the prefix and because this is tagged with Joomla, you might be so kind to post how to construct this query using Joomla query building methods.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;
Also answered here: https://joomla.stackexchange.com/questions/31797/how-to-join-custom-fields-data-to-users-table-without-extra-rows-in-result-set/
fields_values? This question is a little too incomplete.