Skip to content

Instantly share code, notes, and snippets.

@davidvandusen
Created October 18, 2017 20:19
Show Gist options
  • Save davidvandusen/46e277ea4f23e76fa68536115d36ca5d to your computer and use it in GitHub Desktop.
Save davidvandusen/46e277ea4f23e76fa68536115d36ca5d to your computer and use it in GitHub Desktop.
BEGIN;
CREATE TABLE users (
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(255) NOT NULL
);
CREATE TABLE connections (
id SERIAL PRIMARY KEY NOT NULL,
user_id_1 INTEGER NOT NULL,
user_id_2 INTEGER NOT NULL
);
INSERT INTO users (name) VALUES ('a'), ('b'), ('c');
INSERT INTO connections (user_id_1, user_id_2) VALUES
(1, 2),
(2, 3),
(3, 1);
SELECT
friends.id
FROM
users
JOIN connections ON users.id = connections.user_id_1
JOIN users AS friends ON connections.user_id_2 = friends.id
WHERE
users.id = 1
UNION
SELECT
friends.id
FROM
users AS friends
JOIN connections ON friends.id = connections.user_id_1
JOIN users ON connections.user_id_2 = users.id
WHERE
users.id = 1;
ROLLBACK;
BEGIN;
CREATE TABLE users (
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(255) NOT NULL
);
CREATE TABLE connections (
id SERIAL PRIMARY KEY NOT NULL,
created_at TIMESTAMP WITHOUT TIME ZONE
);
CREATE TABLE connections_users (
id SERIAL PRIMARY KEY NOT NULL,
connection_id INTEGER NOT NULL,
user_id INTEGER NOT NULL
);
INSERT INTO users (name) VALUES ('a'), ('b'), ('c');
INSERT INTO connections (created_at) VALUES (now()), (now()), (now());
INSERT INTO connections_users (connection_id, user_id) VALUES
(1, 1),
(1, 2),
(2, 2),
(2, 3),
(3, 3),
(3, 1);
SELECT
friends.id
FROM
users
JOIN connections_users ON connections_users.user_id = users.id
JOIN connections ON connections_users.connection_id = connections.id
JOIN connections_users AS connections_friends ON connections_friends.connection_id = connections.id
JOIN users AS friends ON connections_friends.user_id = friends.id
WHERE
users.id = 1 AND
friends.id <> 1;
ROLLBACK;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment