我在用户管理上添加了一个自定义字段，效果很好，自定义字段保存在fields*fields_values表上，我找不到一种方法来将用户表users表和字段表中的信息连接在一个查询结果中。 我需要类似的东西：SELECT id,NAME,username,customfield FROM users WHERE Id=2 我怎么才能做到这一点？
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)
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;
2条答案
按热度按时间yks3o0rb1#
roejwanj2#
我找到了这个链接：https://ubiq.co/database-blog/transpose-rows-columns-dynamically-mysql/帮助我创建了一个纯MySQL解决方案：
也在这里回答：https://joomla.stackexchange.com/questions/31797/how-to-join-custom-fields-data-to-users-table-without-extra-rows-in-result-set/