loading multiple relations in one query PostgreSQL

Question

There are 4 tables.

1. Snapmails
   • User
   • Snapmail Cameras
     • Cameras

when a user adds a snapmail, let say user's id is 41 and snapmail he created has id of 1. and He created snapmail for 5 cameras, and they have ids as 1, 2, 3, 4, 5

now snapmail in db will look like

ID | user_id | 
1  |   41    |

and snapmail_cameras will have

ID | camera_id | snapmail_id
 1 |     1     |     1
 2 |     2     |     1
 3 |     3     |     1
 4 |     4     |     1
 5 |     5     |     1

I am trying to get all snapmails which is pretty simple, select * from snapmails but I want to have 2 more things in this.

User's name and Camera'name.

I have tried this.

SELECT sm.*, u.firstname || ' ' || u.lastname as fullname,cam.name
FROM snapmails as sm
INNER JOIN users as u ON sm.user_id = u.id
LEFT JOIN snapmail_cameras as sc ON sm.id = sc.snapmail_id
LEFT JOIN cameras as cam ON sc.camera_id = cam.id

This gives me what I want but in a different way. I want to get a snapmail from db such as

 ID  | fullname | camera_names
 1   | Junaid F.|  Camera1, Camera2, Camera3

but my query is giving me as

 ID  | fullname | camera_name
 1   | Junaid F.|  Camera1
 1   | Junaid F.|  Camera2
 1   | Junaid F.|  Camera3
 1   | Junaid F.|  Camera4
 1   | Junaid F.|  Camera5

camera table has a column name and user's table has 2 columns firstname and lastname. My query is giving me all results but one row for each camera but I want to have one row with all camera names. is that possible?

UPDATE: I am working in Ecto Will that possible to wrap this all in Ecto Query?

Answer 1

You can use string_agg:

SELECT sm.id, u.firstname || ' ' || u.lastname as fullname, string_agg(cam.name, ',')
FROM snapmails as sm
INNER JOIN users as u ON sm.user_id = u.id
LEFT JOIN snapmail_cameras as sc ON sm.id = sc.snapmail_id
LEFT JOIN cameras as cam ON sc.camera_id = cam.id
group by 1, 2