LIMIT the results of nested JOIN queries
<p>I honestly have no idea how to give a better title for this :(</p>

<p>Basically I have these 3 tables</p>

<pre><code>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
</code></pre>

<p>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)</p>

<p>What I have so far is the query to select 3 comments for each user</p>

<pre><code>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 &lt;= 3 OR comments.rn IS NULL;
</code></pre>