Как сделать этот sql с запросом array_agg?

Я хочу сделать запрос

select * from projects where user_id = 3;

и в зависимости от его результата r мне нужно сделать n запросов, где n - это длина l из r. например:

| id | project_name | description | user_id |
| 1  | Project A    | lorem ipsu  | 3       |
| 4  | Project B    | lorem ipsu  | 3       |

l => 2 затем:

select * from images where project_id = 1;
select * from images where project_id = 4;

Хорошо, вы можете увидеть, к чему это приведет, если l слишком велико. Слишком много выборок, слишком много обращений к базе данных. Есть ли лучший способ достичь конечного результата, например:

| id | project_name | description | user_id | images           |
| 1  | Project A    | lorem ipsu  | 3       | {imgX,imgY,imgZ} |
| 4  | Project B    | lorem ipsu  | 3       | {imgA,imgB}      |

Я слышал о функции array_agg в postgres. Может быть, это ответ? В любом случае, это мои описания таблиц:

                             Table "public.projects"
   Column    |           Type           |                           Modifiers                       
-------------+--------------------------+-------------------------------------------------------
 id          | integer                  | not null default     nextval('projects_id_seq'::regclass)
 name        | character varying(255)   | 
 description | character varying(255)   | 
 user_id     | integer                  | 
 created_at  | timestamp with time zone | 
 updated_at  | timestamp with time zone | 

                                    Table "public.images"
   Column   |           Type           |                      Modifiers                      
------------+--------------------------+-----------------------------------------------------
 id         | integer                  | not null default nextval('images_id_seq'::regclass)
 name       | character varying(255)   | 
 url        | character varying(255)   | 
 project_id | integer                  | 
 created_at | timestamp with time zone | 
 updated_at | timestamp with time zone |

И заранее спасибо :D


postgresql arrays sql join array-agg
person cesarvargas    schedule 08.06.2016    source источник

Ответы (3)


arrow_upward
2
arrow_downward

array_agg похожа на любую другую агрегатную функцию (счетчик, сумма), но возвращает массив вместо скалярного значения. То, что вам нужно, может быть достигнуто, просто объединив и сгруппировав две таблицы.

SELECT p.id, p.name, p.description, p.user_id, array_agg(i.name) images
FROM projects p
LEFT JOIN images i ON p.id = i.project_id
GROUP BY p.id, p.name, p.description, p.user_id
person Elvis Guimarães    schedule 08.06.2016
comment
Извините, я думал, что это легко понять. Отредактировано. - person Elvis Guimarães; 08.06.2016

arrow_upward
0
arrow_downward

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

SELECT * FROM images
WHERE project_id IN (
  SELECT project_id FROM projects
  WHERE user_id = 3);
person Patrick    schedule 08.06.2016

arrow_upward
0
arrow_downward

Вам нужны записи из projects плюс записи в image names as array, соответствующие project_id :

SELECT *
FROM   projects
LEFT JOIN LATERAL (SELECT array_agg(name) AS images FROM images WHERE project_id = projects.project_id) x ON true
WHERE user_id = '3'

sqlfiddle

person Jose Hermosilla Rodrigo    schedule 08.06.2016