Блог

Пример использования таков: каждый пользователь может создавать свои собственные игры и отслеживать, в какой стране он играл в игру.

Я хотел бы создать один запрос, в котором я мог бы получить список всех игр для этого пользователя и в какой стране была сыграна эта игра. Меня интересует только идентификатор страны.

У меня есть 4 таблицы: пользователи, игры, страны и таблица games_countries_xref.

 CREATE SEQUENCE countries_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1;

CREATE TABLE "public"."countries" (
    "id" integer DEFAULT nextval('countries_id_seq') NOT NULL,
    "name" character varying(200) NOT NULL,
    CONSTRAINT "countries_pkey" PRIMARY KEY ("id")
) WITH (oids = false);


INSERT INTO "countries" ("id", "name") VALUES
(1, 'USA'),
(2, 'Japan'),
(3, 'Australia');

CREATE SEQUENCE games_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 3 CACHE 1;

CREATE TABLE "public"."games" (
    "id" integer DEFAULT nextval('games_id_seq') NOT NULL,
    "user_id" integer NOT NULL,
    "name" character varying(200) NOT NULL,
    CONSTRAINT "games_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

INSERT INTO "games" ("id", "user_id", "name") VALUES
(1, 1,  'Monopoly'),
(2, 1,  'Zelda'),
(3, 2,  'Hide amp; Seek');

CREATE TABLE "public"."games_countries_xref" (
    "game_id" integer NOT NULL,
    "country_id" integer NOT NULL
) WITH (oids = false);

INSERT INTO "games_countries_xref" ("game_id", "country_id") VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 2),
(3, 1);

CREATE SEQUENCE users_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 2 CACHE 1;

CREATE TABLE "public"."users" (
    "id" integer DEFAULT nextval('users_id_seq') NOT NULL,
    "name" character varying(200) NOT NULL,
    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

INSERT INTO "users" ("id", "name") VALUES
(1, 'Jack'),
(2, 'Jason');
 

при запросе данных я попытался использовать ARRAY_AGG:

 WITH country_ids AS (
    SELECT g.user_id, ARRAY_AGG(gcx.country_id) AS country_ids
    FROM games AS g
    LEFT JOIN games_countries_xref AS gcx ON g.id = gcx.game_id
    GROUP BY g.user_id
)
SELECT g.name, country_ids
FROM games AS g
    NATURAL LEFT JOIN country_ids
WHERE g.user_id = 1
 

но это дает мне такой результат:

 name     | country_ids
------------------
Monopoly | {1,2,3,2}
Zelda    | {1,2,3,2}
 

пока я ищу это:

 name     | country_ids
------------------
Monopoly | {1,2,3}
Zelda    | {2}
 

Я знаю, что, скорее всего, делаю что-то не так в подзапросе, но я не могу понять, что именно.
Есть какие-нибудь идеи?

Вы на правильном пути ARRAY_AGG , но немного слишком агрессивны с объединениями. Вам просто нужно простое соединение (1 слева, 1 внутри) на 3 таблицах

     select g.name,array_agg(gcx.country_id) as country_ids  
      from games g 
      join users u on u.id = g.user_id
      left join games_countries_xref gcx on gcx.game_id = g.id
     where u.id = 1
     group by g.name;
 
 ---------- ------------- 
|   name   | country_ids |
 ---------- ------------- 
| Monopoly | {1,2,3}     |
| Zelda    | {2}         |
 ---------- -------------