How to make this sql with query array_agg?

Question

I want to make a query

select * from projects where user_id = 3;

and depending on it's result r, I need to make n queries, where n is the length l of r. eg:

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

l => 2 then:

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

Ok, you can see where this is going if l is too big. Too many selects, too many access to the database. Is there a better way to achieve an end result like so:

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

I heard about array_agg function on postgres. Maybe that's the answer? Anyways, these are my table descriptions:

                             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 |

And thank you in advance :D

Elvis Guimarães · Accepted Answer · 2016-06-08 14:32:11Z

2

array_agg is like any other aggregate function (count, sum), but returns an array instead of a scalar value. What you need can be achieved simply by joining and grouping the 2 tables.

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

edited Jun 8, 2016 at 14:32

answered Jun 8, 2016 at 13:00

Elvis Guimarães

363 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Elvis Guimarães Over a year ago

Sorry, I thought it was easy to understand. Edited.

Jose Hermosilla Rodrigo · Accepted Answer · 2016-06-08 04:56:43Z

1

You want records from projects plus records in image names as array matching by 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

edited Jun 8, 2016 at 4:56

answered Jun 8, 2016 at 4:44

Jose Hermosilla Rodrigo

3,7136 gold badges25 silver badges38 bronze badges

Comments

Patrick · Accepted Answer · 2016-06-08 04:16:51Z

0

Probably the easiest solution for you is a sub-select. This comes closest to the individual SELECT statements that you mention earlier:

SELECT * FROM images
WHERE project_id IN (
  SELECT project_id FROM projects
  WHERE user_id = 3);

answered Jun 8, 2016 at 4:16

Patrick

33k7 gold badges73 silver badges102 bronze badges

Collectives™ on Stack Overflow

How to make this sql with query array_agg?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related