friends1.sql

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;

friends2.sql

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;