Блог

Главная - Вопросы по программированию - Извлечение связанного набора данных «многие ко многим» в единую структуру

Извлечение связанного набора данных «многие ко многим» в единую структуру

#sql #database #postgresql #go

# #sql #База данных #postgresql #Вперед

Вопрос:

Я пытаюсь встроить систему авторизации RBAC в приложение. Пользователи, роли и разрешения хранятся в базе данных. Каждая роль может быть назначена каждому пользователю. Вот почему у меня есть user_account таблица и role таблица, склеенные в отношения «многие ко многим» с помощью таблицы user_role соединений. Урезанная настройка базы данных выглядит следующим образом:

 CREATE TABLE user_account (
    id    uuid NOT NULL DEFAULT uuid_generate_v1mc(),
    email character varying(128) NOT NULL,
    CONSTRAINT user_account_pkey PRIMARY KEY (id),
    CONSTRAINT user_account_email_key UNIQUE (email)
);

CREATE TABLE role (
    id   uuid NOT NULL DEFAULT uuid_generate_v1mc(),
    name character varying(128) NOT NULL,
    CONSTRAINT role_pkey PRIMARY KEY (id),
    CONSTRAINT role_name_key UNIQUE (name)
);

CREATE TABLE user_role (
    user_id uuid NOT NULL,
    role_id uuid NOT NULL,
    CONSTRAINT user_role_pkey PRIMARY KEY (user_id, role_id),
    CONSTRAINT user_role_fkey_user FOREIGN KEY (user_id) REFERENCES user_account (id) ON DELETE CASCADE,
    CONSTRAINT user_role_fkey_role FOREIGN KEY (role_id) REFERENCES role (id) ON DELETE CASCADE
);

Очевидно, я настроил соответствующие структуры go:

 type User struct {
    ID    string `db:"id"`
    Email string `db:"email"`
}

type Role struct {
    ID   string `db:"id"`
    Name string `db:"name"`
}

Пока все хорошо. Извлечение одной или нескольких строк по отдельности в соответствующую структуру go не является проблемой. Но я бы предпочел получить пользователя и все назначенные ему роли. Результирующая структура go будет выглядеть примерно так:

 type User struct {
    ID    string `db:"id"`
    Email string `db:"email"`
    Roles []Role 
}

Я спрашиваю: какова наилучшая стратегия для извлечения данных в структуру?

  1. Извлеките пользовательскую запись с помощью запроса, извлеките все назначенные ей роли с помощью другого запроса и создайте User структуру.

или

  1. Извлечение записи пользователя и всех назначенных ему ролей с помощью одного запроса.

Я знаю, как сделать первый случай, но борюсь со вторым. Я использую sqlx и PostgreSQL. Возможно ли вообще то, чего я пытаюсь достичь? Если да, то как? Какой db тег использовать?

Мой наивный запрос для второго подхода выглядит следующим образом, но, очевидно, работает не так, как предполагалось:

 SELECT user_account.id, user_account.email, role.id, role.name
  FROM user_account
LEFT OUTER JOIN user_role
  ON user_account.id = user_role.user_id
LEFT OUTER JOIN role
  ON user_role.role_id = role.id

Есть предложения?

Метки: