Как заставить mysql Group_Concat работать с дополнительной информацией CONCAT?

Question

Как заставить mysql Group_Concat работать с дополнительной информацией CONCAT?

Я работаю над веб-приложением с mysql, включающим таблицу ролей пользователей, в которой хранятся уровни разрешений, а также идентификаторы ролей и связанные с именами пользователей и их идентификаторами (избыточность для продолжения поддержки устаревшего кода).

Я хочу иметь возможность отображать роль каждого пользователя в виде списка, разделенного запятыми, на простом английском языке с минимальным попаданием в БД (> 1 тыс. Пользователей), а также уровнем их разрешений.

Разрешения хранятся в виде битов в столбцах ismanager, а столбцы ishead и ролей-идентификаторы привязываются к define_roles (id, rolename).

Вот что у меня работает (но не показывает уровни пользователя)

<?
$rolenames = array();
foreach($db->query("SELECT id, rolename FROM define_roles") as $row)
{
$rolenames[$row['id']] = $row['rolename'];
}

foreach($db->query("SELECT DISTINCT userid, username,
GROUP_CONCAT(DISTINCT roleid  ORDER BY roleid) AS idlist
FROM user_roles
GROUP BY userid
ORDER BY username ASC") as $row)
{
$rolestring = '';
//echo $row['idlist'];
foreach(explode(',',$row['idlist']) as $id)
{
$rolestring .= " ".$rolenames[$id].",";
}
$rolestring = rtrim($rolestring,',');

echo "<tbody>
<tr>
<td><font size='1'>" . $row['username'] . "</font></td>
<td><font size='1'>" .$rolestring. "</font></td>
</tr>";
}
?>

Выход:

TestUser Sales
UserTest2 Продажи, Почта, Уборка

Что я хочу увидеть

TestUser Sales (менеджер)
UserTest2 Sales, Mailroom (руководитель), уборщик (менеджер)

Лучшее, что я мог придумать (хотя это не работает):

$rolenames = array();
foreach($db->query("SELECT id, rolename FROM define_roles") as $row)
{
$rolenames[$row['id']] = $row['rolename'];
$rolenames[$row['id']."10"] = $row['rolename']." (Manager)";
$rolenames[$row['id']."01"] = $row['rolename']." (Head)";
$rolenames[$row['id']."11"] = $row['rolename']." (Head)";
}

foreach($db->query("SELECT DISTINCT userid, username,
GROUP_CONCAT
(
CONCAT(roleid,ismanager,ishead) ORDER BY roleid
) AS idlist
FROM user_roles
GROUP BY userid
ORDER BY username ASC") as $row)

3

concat group-concat mysql php

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Во-первых, вам, вероятно, следует нормализовать базу данных, чтобы у вас была отдельная таблица, связывающая пользователей и определенные роли. Но это отдельная вещь, и я ценю, что вы не сможете этого сделать.

Если я правильно понял, что находится в ваших таблицах, ответ таков:

SELECT
userid,
username,
GROUP_CONCAT(DISTINCT
CONCAT(
(SELECT d.rolename FROM define_roles AS d
WHERE d.id = roleid)
),
IF(ismanager = 1, " (Manager)", ""),
IF(ishead = 1, " (Head)", "")
) AS roles
FROM user_roles
GROUP BY userid;

Вам не понадобится исходный sql, где вы искали ролевые имена из define_roles.

[РЕДАКТИРОВАТЬ]: Извините, обновлено с помощью скобок вокруг внутреннего SELECT.

2