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

Question

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

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

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

Разрешения хранятся в виде битов в столбцах ismanager и ishead, а идентификаторы ролей введено значение define_roles (идентификатор, имя роли)

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

<?
$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>";
}
?>

Вывод:

Продажи тестовых пользователей
usertest2 Продажи, Почтовое отделение, Уборка помещений

То, что я хочу увидеть, это

Продажи тестовых пользователей (менеджер)
Продажи usertest2, Отдел почты (Руководитель), Уборщик (Менеджер)

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

$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

php mysql group-concat concat

Author: Jay Blanchard, 2015-01-30

Source

1 answers

score 2 · 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.

[РЕДАКТИРОВАТЬ]: Извините, обновлено скобками вокруг внутреннего ВЫБОРА.