SELECT posts and their comments

0
votes

I have three tables: users, posts, and comments.

How do I SELECT the first three posts, each with their comment count and first three comments?

Example: SQL Fiddle

Goal is to build a table like the one below

  • The first row of each post_id grouping is the post
  • The comment_id is that post's comment count
  • The next rows are the first three comments for that post

Expected output:

-- post_id   comment_id   user_id   body               created_at
-- 1         4            1         Hello. I'm Jane.   August, 28 2016 14:12:01
-- 1         1            2         Nice post, Jane.   August, 28 2016 14:12:01
-- 1         2            1         Thank you, John.   August, 28 2016 14:12:01
-- 1         3            2         You're welcome.    August, 28 2016 14:12:01
-- 2         2            1         This is post 2.    August, 28 2016 14:12:01
-- 2         5            2         I like this.       August, 28 2016 14:12:01
-- 2         6            1         Why, thank you.    August, 28 2016 14:12:01
-- 3         0            1         This is post 3.    August, 28 2016 14:12:01
3
sqlpostgresqljoingreatest-n-per-group
Do you mean the first three posts period, or the first three posts for each user ? And better to put table schema in the post here on SO as well as in a link... - Charles Bretana
That is, the output should be 9 rows ? post1-comm1, post1-comm2, post1-comm3, post2-comm1, ... - Mike
@CharlesBretana I meant the first three posts period. I actually do want to get the first three posts for a specified (not for each) user. But, I left that part out because I wanted to simplify my question, and I figured I'd know how to modify the query to work for the posts of a specific user rather than all the posts. - ma11hew28
@Mike no, the output could be anywhere from 0 to 12 rows. See this answer stackoverflow.com/a/39192948/242933 for an understanding of the expected output. - ma11hew28
"I meant the first three posts period. I actually do want to get the first three posts for a specified (not for each) user". Those are exactly the two different options I am asking you to choose between. The first three posts period would be across the setoff all posts from all users. I think you want the first three posts for each user, (or for a specified user) - Charles Bretana

3 Answers

1
votes

Understanding the logic behind the expected output

You have a pretty sophisticated demand for your expected output. From what I understood from your sql fiddle looking at expected result, you want to:

  1. get first three posts
  2. get first three comments for them
  3. append the result from 1 and 2 with different logic for columns comment_id and body

The difference in logic seems to be like below:

For each row representing a post:

  • in comment_id store number of comments for that post
  • in user_id store the user that wrote a post
  • in body store the body of the post
  • in created_at store the timestamp when the post was created

While for each row representing comment the logic is analogical (but for a comment, not a post) with the exception of comment_id column where you want to store the comment id.

Query and explanation

For live example look at SQL fiddle

First, take first three posts and build rows for them counting comments for each one. Then, union those posts rows with comment rows, and use row_number() function to limit comment rows in the output to maximum of 3 per a post.

Assigning 0 as row number for posts means they fulfill the condition of rn <= 3.

To order the output as you wish so that for every post their comments are sorted right after them I've added an order_column to be able to include it in ORDER BY.

WITH first_posts AS (
    SELECT p.id AS post_id, COUNT(c.id) AS comment_id, p.user_id, p.body, p.created_at
    FROM (SELECT * FROM posts ORDER BY id LIMIT 3) AS p
    LEFT JOIN comments AS c
    ON p.id = c.post_id
    GROUP BY 1, 3, 4, 5
)
SELECT post_id, comment_id, user_id, body, created_at
FROM (
    SELECT 1 AS type, post_id, comment_id, user_id, body, created_at, 0 AS r
    FROM first_posts
    UNION ALL
    SELECT 2 AS type, p.post_id, c.id, c.user_id, c.body, c.created_at, 
        ROW_NUMBER() OVER (PARTITION BY p.post_id ORDER BY c.id) AS r
    FROM first_posts AS p
    INNER JOIN comments AS c
    ON p.post_id = c.post_id
    ORDER BY post_id, type, comment_id
) AS f
WHERE r <= 3;
1
votes

You can use a subquery to limit your selection to the first 3 posts and row_number to only include the first 3 comments per post:

SELECT * FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY c.post_id ORDER BY c.id) rn
    FROM posts p
    JOIN comments c ON c.post_id = p.id
    WHERE p.id IN (SELECT id FROM posts ORDER BY id LIMIT 3)
) t WHERE rn <= 3

or if you want the first 3 posts & comments per user

SELECT * FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY c.post_id ORDER BY c.id) comments_rn,
        ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.id) post_rn        
    FROM posts p
    JOIN comments c ON c.post_id = p.id
    JOIN users u ON u.id = p.user_id
) t WHERE comments_rn <= 3 and post_rn <= 3
1
votes

edited Syntax and column names:

Select id, name, p.*, c.* 
from users u
    join posts p on p.User_Id = u.Id
       and (Select count(*) From posts 
            where user_Id = u.Id
               and created_at <= p.created_at) <= 3
    join comments c on c.post_Id = p.Id
       and (Select count(*) From comments 
            where post_Id = p.Id 
               and created_at <= c.created_at) <= 3