Last active
April 6, 2024 16:53
-
-
Save alaindet/482ad020ccbca862ef711cfe69fae1dd to your computer and use it in GitHub Desktop.
SQLite: Example for putting a many-to-many relationship in a single column
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE "users" ( | |
"id" INTEGER NOT NULL UNIQUE, | |
"email" TEXT NOT NULL UNIQUE, | |
PRIMARY KEY("id" AUTOINCREMENT) | |
); | |
INSERT INTO "users" ("id", "email") VALUES | |
(1, "alice@example.com"), | |
(2, "bob@example.com"), | |
(3, "charlie@example.com"); | |
CREATE TABLE "roles" ( | |
"id" INTEGER NOT NULL UNIQUE, | |
"name" TEXT NOT NULL UNIQUE, | |
PRIMARY KEY("id" AUTOINCREMENT) | |
); | |
INSERT INTO "roles" ("id", "name") VALUES | |
(1, "Admin"), | |
(2, "Moderator"), | |
(3, "User"); | |
CREATE TABLE "roles_users" ( | |
"userid" INTEGER, | |
"roleid" INTEGER, | |
PRIMARY KEY ("userid", "roleid"), | |
FOREIGN KEY ("userid") REFERENCES "users" ("id"), | |
FOREIGN KEY ("roleid") REFERENCES "roles" ("id") | |
); | |
INSERT INTO "roles_users" ("roleid", "userid") VALUES | |
(1, 1), | |
(2, 1), | |
(2, 2), | |
(3, 1), | |
(3, 2), | |
(3, 3); | |
-- v1 from https://stackoverflow.com/a/56869075 | |
SELECT | |
u.id AS user_id, | |
u.email, | |
IFNULL(u_roles.role_name, '') AS role_name | |
FROM users u LEFT JOIN ( | |
SELECT | |
GROUP_CONCAT(DISTINCT r.name) AS role_name, | |
ru.userid | |
FROM | |
roles AS r | |
INNER JOIN roles_users AS ru ON r.id = ru.roleid | |
GROUP BY | |
ru.userid | |
) u_roles ON u.id = u_roles.userid; | |
-- v2 (assumes all users have a role) | |
SELECT | |
u.id AS user_id, | |
u.email, | |
userroles.roles | |
FROM | |
users u | |
JOIN ( | |
SELECT | |
ru.userid, | |
GROUP_CONCAT(DISTINCT r.name) AS roles | |
FROM | |
roles AS r | |
JOIN roles_users AS ru ON r.id = ru.roleid | |
GROUP BY | |
ru.userid | |
) AS userroles ON u.id = userroles.userid; | |
-- v3 Thanks to @mnlttt | |
SELECT | |
u.id, | |
u.email, | |
GROUP_CONCAT(r.name) AS roles | |
FROM users AS u | |
JOIN roles_users AS ru ON u.id = ru.userid | |
JOIN roles AS r ON ru.roleid = r.id | |
GROUP BY u.id, u.email |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment