Kunkka Kunkka - 2 years ago 77
SQL Question

USING limit/offset in a JOIN query

I have 4 tables

  1. A user account

    user_id | username | password

  2. A projects table

    project_id | project_name | category_id

  3. A user_projects table (many to many relationship)

    accounts_projects_id | account_id | project_id

  4. A project_messages table (a project will have many messages)

    message_id | project_id |message| username

At login, I'm running a query where I fetch the number of projects a user belongs to and the messages for each project using the below query

SELECT account.user_id,account.username,
array_agg(json_build_object('message',project_messages.message,'username',project_messages.username)) AS messages,
FROM account
JOIN accounts_projects ON account.user_id = accounts_projects.account_id
JOIN project_messages ON accounts_projects.project_id = project_messages.project_id
JOIN project ON project.project_id = accounts_projects.project_id
WHERE account.username=$1
GROUP BY project.project_name,account.user_id

this gives me the below output

userid,username, messages (json array object),project_name`
87;"kannaj";"{"{\"message\" : \"saklep\", \"username\" : \"kannaj\"}"}";"Football with Javascript"
87;"kannaj";"{"{\"message\" : \"work\", \"username\" : \"kannaj\"}","{\"message\" : \"you've been down to long in the midnight sea\", \"username\" : \"kannaj\"}","{\"message\" : \"Yeaaaa\", \"username\" : \"house\"}"}";"Machine Learning with Python"
87;"kannaj";"{"{\"message\" : \"holyy DIVVEERRR\", \"username\" : \"kannaj\"}"}";"Beethoven with react"

Is there a way I can use the LIMIT/OFFSET function when retrieving the messages from the project_messages table?

Answer Source

To make our examples simpler lets say we have two linked tables:

t2(id, t1_id);

And query is

select t1.id, array_agg(t2.id)
from t1 join t2 on (t1.id = t2.t1_id)
group by t1.id;

It is very simplified variant of the your large query as you can see.

1) Arrays

select t1.id, (array_agg(t2.id order by t2.id desc))[3:5]
from t1 join t2 on (t1.id = t2.t1_id)
group by t1.id;

This query works just as original, but returns only from 3,4 and 5 elements of the array which is equal to offset 2 limit 3.

2) Subquery and lateral

  t1 join lateral 
    (select t2.id as x from t2 where t1.id = t2.t1_id order by t2.id desc offset 2 limit 3) t on (true)
group by t1.id;

Here lateral keyword allows to use fields from other tables mentioned in the main from clause in our subquery (t1.id).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download