sql - 将多对多相关数据集提取到单个结构中

Question

我尝试将 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结构。

或者

2. 使用单个查询获取用户记录及其所有分配的角色。

我知道如何做第一个案例，但在第二个案例中挣扎。我正在使用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

有什么建议么？

标签： sqldatabasepostgresqlgo