Efficient query to join three tables

Question

My app has a users table, a teams table, a teams_users table, and an assessments table. Oversimplified:

**users**
id

**teams**
id

**teams_users**
team_id
user_id

**assessments**
id
user_id
team_id

A user can have many assessments.

When I grab users in a certain context, I want to grab their associated assessments as well (the whole object), plus, as a value on the returned user, the team_ids which are essentially the result of plucking the team_id column for all rows in the teams_users table with the user's user_id.

I know how to do each of these queries separately, but I'm unsure of how much I can combine into one efficient query, and even more unsure how to do that, as I'm pretty rusty with SQL. My first thought was something like this:

SELECT users.id, users.name, users.email, users.role
FROM users
INNER JOIN assessments ON assessments.user_id = users.id
WHERE users.id='someID'

In that query, though, I'm not actually doing anything with the assessments. So I was considering something like this:

SELECT users.id, users.name, users.email, users.role, assessments
FROM users
INNER JOIN assessments ON assessments.user_id = users.id
WHERE users.id='someID'

This SORT of works, but returns the assessments as a parenthesis-delimited array of values (I'm using JavaScript, and the pg library otherwise parses responses into JSON), which it seems better to fix on a SQL level than to parse into JSON:

{
    id: 'mEPRVHp3',
    name: 'Frank',
    role: 'admin',
    assessments: '(uibbH0ff,rPWZDIoA,mEPRVHp3,99,2017-12-01)' 
}

To clarify, I'd prefer it like so:

{
    id: 'mEPRVHp3',
    name: 'Frank',
    role: 'admin',
    assessments: [
     { 
       id: 'uibbH0ff',
       team_id: 'rPWZDIoA',
       user_id: 'mEPRVHp3',
       score: 99,
       date: '2017-12-01'
     }
   ]
}

Then separately, I'd love to grab the teams_users team_ids in the same query. So that the end response is something like this:

{
    id: 'mEPRVHp3',
    name: 'Frank',
    role: 'admin',
    assessments: [
     { 
       id: 'uibbH0ff',
       team_id: 'rPWZDIoA',
       user_id: 'mEPRVHp3',
       score: 99,
       date: '2017-12-01'
     }
   ],
   team_ids: ['rPWZDIoA']
}

Tried to add teams_users.team_id AS team_ids to the end of the SELECT statement, but that broke the whole query.

Again, not sure if that's possible, and in this case, not at ALL sure how to do it.

Any thoughts? Working on my limited SQL skillz.

Thanks!

Answer 1

Assuming you are using PostgreSQL version 9.3 or newer.

I would use row_to_json() to "format" the assessment record. Then I would use a lateral join to get your team ids.

Like so:

SELECT
    usr.id,
    usr.name, 
    usr.email,
    usr.role,
    row_to_json(asmnt) AS assessments, --Use row_to_json()
    tusr.team_ids
FROM
    users usr
INNER JOIN
    assessments asmnt
    ON asmnt.user_id = usr.id
INNER JOIN LATERAL
    (
    SELECT
        array_agg(teams_users.team_id) AS team_ids
    FROM
        teams_users
    WHERE
        teams_users.user_id = usr.id
    ) tusr
    ON TRUE
WHERE
    usr.id = 'someID';