CODEWITHSUNDEEP
sqlpostgresql
Tan Nguyen
Tan Nguyen

Reputation: 3354

LIMIT the results of nested JOIN queries

I honestly have no idea how to give a better title for this :(

Basically I have these 3 tables

Table "public.users"
Column |         Type          |                     Modifiers
--------+-----------------------+----------------------------------------------------
id     | integer               | not null default nextval('users_id_seq'::regclass)
name   | character varying(40) |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "comments" CONSTRAINT "comments_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE

Table "public.comments"
Column  |  Type   |                       Modifiers
---------+---------+-------------------------------------------------------
id      | integer | not null default nextval('comments_id_seq'::regclass)
user_id | integer |
content | text    |
Indexes:
"comments_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"comments_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE

Table "public.votes"
Column   |  Type   |                     Modifiers
------------+---------+----------------------------------------------------
id         | integer | not null default nextval('votes_id_seq'::regclass)
up         | boolean | default false
comment_id | integer |
Indexes:
"votes_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"votes_comment_id_fkey" FOREIGN KEY (comment_id) REFERENCES comments(id) ON UPDATE CASCADE ON DELETE CASCADE

I want to select all users (including those who don't have any comment) and include 3 comments for each user and then for each comment select 2 votes (including those comments that don't have any vote)

What I have so far is the query to select 3 comments for each user

SELECT users.id as userId, comments.id as commentId, users.name, comments.content, comments.rn
FROM users
LEFT JOIN (
  SELECT *, row_number() OVER (PARTITION BY comments.user_id) as rn FROM comments
) as comments
ON users.id = comments.user_id
WHERE comments.rn <= 3 OR comments.rn IS NULL;

Upvotes: 4

Views: 102

Answers (3)

Abelisto
Abelisto

Reputation: 15614

Probably it will be more index-friendly:

select
  *
from
  users u
    left join lateral (select * from comments c where u.id = c.user_id order by c.id limit 3) c on (u.id = c.user_id)
      left join lateral (select * from votes v where c.id = v.comment_id order by v.id limit 2) v on (c.id = v.comment_id)

Upvotes: 2

FuzzyTree
FuzzyTree

Reputation: 32392

Because users to comments and comments to votes both have 1-to-many relationships, doing both joins will give you CxV (3x2=6) rows per user.

Because comments will be duplicated when a comment has more than 1 vote, use dense_rank() instead of row_number() so that "duplicate" comments will have the same rank and be included in the rn < 4 criteria

select * from (
    select *,
        dense_rank() over (partition by u.id order by c.id) rn,
        dense_rank() over (partition by c.id order by v.id) rn2
    from users u
    left join comments c on c.user_id = u.id
    left join votes v on v.comment_id = c.id
) t where rn < 4 and rn2 < 3

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You have the right idea. Just continue with it:

SELECT u.id as userId, c.id as commentId, u.name, c.content, c.rn
FROM users u LEFT JOIN
     (SELECT c.*,
             ROW_NUMBER() OVER (PARTITION BY c.user_id) as rn
      FROM comments c
     ) c
    ON u.id = c.user_id LEFT JOIN
    (SELECT v.*,
            ROW_NUMBER() OVER (PARTITION BY v.comment_id) as rn
     FROM votes v
    ) v
    ON c.id = v.comment_id
WHERE (c.rn <= 3 OR c.rn IS NULL) and
      (v.rn <= 2 or v.rn IS NULL);

Upvotes: 2

Related Questions