Is there a better way I can structure my tables, do a 4 table join, or will I have to perform multiple queries?

What is the correct way to perform this GET request? I currently have 4 tables:

events, event_comments, event_photos, event_users_going

The last 3 tables, event_comments, event_photos, and event_users_going all have a foreign key that uses events table’s primary key.

What I am unsure about is, would I have to do a JOIN on all 4 tables or 4 individual queries? Because, each query on each table returns a different number of rows.

For example the first query would be on events:

SELECT * FROM events where e_id = 1

this returns 1 row.

The 2nd query:

SELECT * FROM event_photos where e_id = 1

this returns 12 rows

3rd Query:

SELECT * FROM event_comments WHERE e_id = 1

this returns 20 rows

4th Query:

SELECT * FROM event_users_going WHERE e_id = 1

this returns 30 rows

Answer

It depends on what is receiving the results. If it can process JSON and the overhead of doing so isn’t a deal-breaker, you can do the following to include related rows as JSON arrays:

SELECT *,
       COALESCE((
         SELECT json_agg(event_photos)
           FROM event_photos
          WHERE event_photos.e_id = events.e_id
       ), '[]') AS photos,
       COALESCE((
         SELECT json_agg(event_comments)
           FROM event_comments
          WHERE event_comments.e_id = events.e_id
       ), '[]') AS comments,
       COALESCE((
         SELECT json_agg(event_users_going)
           FROM event_users_going
          WHERE event_users_going.e_id = events.e_id
       ), '[]') AS going
  FROM events
 WHERE e_id = 1;

Sample output:

 e_id |          photos          |        comments         | going 
------+--------------------------+-------------------------+-------
    1 | [{"e_id":1,"url":"p1"}, +| [{"e_id":1,"txt":"c1"}] | []
      |  {"e_id":1,"url":"p2"}]  |                         | 
(1 row)

When returning JSON, you can take it a step further and build a result with a single column:

SELECT json_build_object(
         'event', events,
         'photos', COALESCE((
           SELECT json_agg(event_photos)
             FROM event_photos
            WHERE event_photos.e_id = events.e_id
         ), '[]'),
         'comments', COALESCE((
           SELECT json_agg(event_comments)
             FROM event_comments
            WHERE event_comments.e_id = events.e_id
         ), '[]'),
         'going', COALESCE((
           SELECT json_agg(event_users_going)
             FROM event_users_going
            WHERE event_users_going.e_id = events.e_id
         ), '[]')
       )
  FROM events
 WHERE e_id = 1;

Sample output:

                              json_build_object                               
------------------------------------------------------------------------------
 {"event" : {"e_id":1}, "photos" : [{"e_id":1,"url":"p1"},                   +
  {"e_id":1,"url":"p2"}], "comments" : [{"e_id":1,"txt":"c1"}], "going" : []}
(1 row)