Как получить данные из нескольких связанных таблиц в Postgres?

Question

[hbrmdc]

Postgres 9.4
4 таблицы связаны между собой many-to-many. Я создал дополнительную таблицу чтобы реализовать эти связи:

CREATE TABLE "public"."relation" (
      "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
      "table1" uuid NOT NULL,
      "table2" uuid,
      "table3" uuid,
      "table4" uuid,
      "approved" bool DEFAULT true,
      CONSTRAINT "relation_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE,
      CONSTRAINT "table1" FOREIGN KEY ("table1") REFERENCES "public"."table1" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
      CONSTRAINT "table2" FOREIGN KEY ("table2") REFERENCES "public"."table2" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
      CONSTRAINT "table3" FOREIGN KEY ("table3") REFERENCES "public"."table3" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
      CONSTRAINT "table4" FOREIGN KEY ("table4") REFERENCES "public"."table4" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
    )
    WITH (OIDS=FALSE);
    ALTER TABLE "public"."relation" OWNER TO "postgres";

Мне нужно получить одну строку из `table1`, включая все связанные строки из других таблиц в виде JSON объекта.
С помощью этого кода я получил строки из таблицы `relation`

SELECT t.*
       FROM ( SELECT table1.id,
                (select row_to_json(relations.*) as array_to_json
                  from(select * from relation where table1 = table1.id) relations
                ) as relations,
                
               from public.table1) t

Но я не могу придумать, как эффективно получить строки из связанных таблиц с помощью данных из таблицы `relation`

Возможно это важно:
каждая строка в таблице `relation` содержит по две связи - например, со table1 и table3, остальные столбцы в этой строке пусты (кроме id, конечно).
Каждая строка в таблицах table1,2,3,4 содержит не много связей: от 1 до 10

Благодарю вас за уделенное время!

Answer 1

[romy4]

Как это вы не знаете про JOIN?
> в виде JSON объекта.
делайте это на принимающей стороне

Comments

[hbrmdc]

про join знаю, но тут же не один столбец со связью. Как правильно сделать? проверять не пуст ли столбец и делать join? Или сделать join для всех? Как сделать так, чтобы joinу не подвергался тот столбец, который связан с таблицей table1? (или с другой, в зависимости от того, из какой таблицы изначально запрос начинается)

[romy4]

hbrmdc: > Как сделать так, чтобы joinу не подвергался тот столбец, который связан с таблицей table1
тут малёха не понял.

[hbrmdc]

пример: Мне нужно получить одну строку из `table1`, включая все связанные строки из других таблиц в виде JSON объекта.
Пример одной релевантной строки из таблицы 'relation':
{
id: 'uuid',
table1: 'id_строки_из_table1',
table2: null,
table3: null
table4: 'id_строки_из_table4',
}
в этом конкретном случае мне нужно получить только строку из table4 по id_строки_из_table4. Мне не нужна строка из table1, так как она уже получена (в этой ситуации как материнский объект

[romy4]

hbrmdc: не включайте в join те таблицы, которые не нужны

[hbrmdc]

из каждой строки нужна только одна таблица, но я не могу предсказать, какая именно

[romy4]

hbrmdc: а где лежит условие из какой таблицы брать строку?

[hbrmdc]

в данный момент нигде, все строки из всех таблиц могут быть переплетены как угодно. Разве что одно важное условие - на одну строку в таблице 'relation' приходится только две связи - между таблицей 1 и 2 или 2 и 4 или 3 и 1 и тд

[romy4]

hbrmdc: тут скорее надо получать всё, а потом уже отсеивать. Кажется, слишком большое усложнение. Проще на клиенте условия прописать.

[hbrmdc]

теперь я не понял - какие именно условия прописывать на клиенте?

[romy4]

hbrmdc: ответ будет вроде
1, 'строка_из_т1',null,null,'строка_из_т4'
2, 'строка_из_т1',null,'строка_из_n3',null
3, null, 'строка_из_т2',null,'строка_из_т4'
4, null, 'строка_из_т2', 'строка_из_т4',null
а дальше по усмотрению заворачиваете в json и удаляете лишнее. Это не задача базы заниматься такой фильтрацие, её задача как можно быстрее выдавать данные.

[hbrmdc]

> её задача как можно быстрее выдавать данные
согласен. Именно по этому я хочу сделать так, чтобы бд совершала одно обращение на каждую строку, а не 4.
Если я создам еще один столбец в таблице 'relation', который будет содержать названия двух таблиц, с которыми эта строка связана, возможно ли сделать что-то вроде:
взять string (table1table4) из этого столбца, удалить из этой string название материнской таблицы (table1table4 -table1 = table4), найти столбец в текущей строке, чье название соответствует остаткам string (table4) и достать из него нужный идентификатор строки из таблицы table4

Это будет лучше, чем поиск по 4 таблицам вместо одной?

[romy4]

hbrmdc: это будут разные запросы, в один вы не впихнёте так. Нет выборочного джойна. Либо по всем сразу, либо отдельные запроса по каждой связи. С объединением всех быстрее.