SQLiteの中間テーブル

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS like_collection;

DROP TABLE IF EXISTS users;

DROP TABLE IF EXISTS colors;

CREATE TABLE users(
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

CREATE TABLE colors(
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

CREATE TABLE like_collection(
  user_id INTEGER NOT NULL,
  color_id INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  PRIMARY KEY (user_id, color_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (color_id) REFERENCES colors(id)
);

INSERT INTO users(name)
VALUES ('Alice'), ('Bob');

INSERT INTO colors(name)
VALUES ('Red'), ('Blue'), ('Green');

INSERT INTO like_collection(user_id, color_id)
VALUES (1, 2), (1, 3), (2, 3);

SELECT users.name AS user__name,
  colors.name AS color__name,
  like_collection.created_at,
  like_collection.updated_at 
FROM users
  INNER JOIN like_collection ON users.id = like_collection.user_id
  INNER JOIN colors ON like_collection.color_id = colors.id;

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS follow_collection;

DROP TABLE IF EXISTS users;

CREATE TABLE users(
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

CREATE TABLE follow_collection(
  following_id INTEGER NOT NULL,
  followers_id INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  PRIMARY KEY (following_id, followers_id),
  FOREIGN KEY (following_id) REFERENCES users(id),
  FOREIGN KEY (followers_id) REFERENCES users(id)
);

INSERT INTO users(name)
VALUES ('Alice'), ('Bob'), ('Carol'), ('Dave'), ('Eve');

INSERT INTO follow_collection(following_id, followers_id)
VALUES (1, 2), (1, 3), (2, 3), (4, 5), (5, 4);

SELECT following__users.name AS following__name,
  followers__users.name AS followers__name,
  follow_collection.created_at,
  follow_collection.updated_at 
FROM users AS following__users
  INNER JOIN follow_collection ON following__users.id = follow_collection.following_id
  INNER JOIN users AS followers__users ON follow_collection.followers_id = followers__users.id;

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS follow_collection;

DROP TABLE IF EXISTS users;

CREATE TABLE users(
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

CREATE TABLE follow_collection(
  following_id INTEGER NOT NULL,
  followers_id INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  PRIMARY KEY (following_id, followers_id),
  FOREIGN KEY (following_id) REFERENCES users(id),
  FOREIGN KEY (followers_id) REFERENCES users(id)
);

INSERT INTO users(name)
VALUES ('Alice'), ('Alice'), ('Alice'), ('Bob'), ('Bob'), ('Carol');

WITH cte__following AS (
  SELECT id AS following_id
  FROM users
  WHERE name == 'Alice'
),
cte__followers AS (
  SELECT id AS followers_id
  FROM users
  WHERE name == 'Bob'
)
INSERT INTO follow_collection(following_id, followers_id)
SELECT *
FROM cte__following
  LEFT OUTER JOIN cte__followers;

SELECT *
FROM follow_collection;