how to load 2 related datasets together? (i.e posts and comments)

Question

I'm fairly new to pg and trying to figure out what the best approach is to loading a set of posts and their associated comments together.

For example: I'm trying to fetch a 10 posts and comments associated with all those posts, like facebooks wall where you see a feed of posts and comments loaded on the same page. My Schema looks something like this:

Posts
--------
id  -  author   -  description  -  date   -  commentCount 

Comments
-------
id  -   post_id  -  author  -  description   -   date

I tried to load both posts and comments on the same postgres function doing the follow:

select *
from posts
LEFT join comments on posts.id = comments.post_id

unfortunately it duplicated the posts N times where comment exists, where N is the number of comments a post has. However, the first solution is that I can always filter it out in Node after fetching the data

Also when I try to use group by posts.id (to make it easier to traverse in node) I get the following error:

column "comments.id" must appear in the GROUP BY clause or be used in an aggregate function

The second thing I can try is to send an array of post_ids I want to load and have pg_function load and send them back, but I can't quite the query right:

CREATE OR REPLACE FUNCTION "getPosts"(postIds int[])
  RETURNS text AS
$BODY$
BEGIN
    RETURN (
        SELECT * 
        FROM Comments
        WHERE Comments.id = postIds[0]
    );
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

to call it:

SELECT n FROM "public"."getPosts"(array[38]) As n;

However, even when trying to get value from one index I get the following error:

ERROR:  subquery must return only one column
LINE 1: SELECT (
               ^
QUERY:  SELECT (
        SELECT * 
        FROM Comments
        WHERE Comments.id = 38
    )

Finally, the last solution is to simple make N seperate calls of postgres, where N is the number of posts with comments, so if I have 5 posts with comments I make 5 calls to postgres with post_id and select from Comments table.

I'm really not sure what to do here, any help would be appreciated.

Thanks

The first solution is the simplest and fastest. The other will need two queries and two trips to the server. About _duplicated_ posts that is the way it is with relational databases and it is a "problem" better dealt with in the presentation layer. — Clodoaldo Neto, May 05 '15 at 18:42
@ClodoaldoNeto is there anyway to use group by without having that aggregate error show up? — Saad, May 05 '15 at 18:54

score 0 · Accepted Answer · answered May 05 '15 at 19:23

To have all comments as an array of records for each post:

select
    p.id, p.title, p.content, p.author,
    array_agg(c) as comments
from
    posts p
    left join
    comments c on p.id = c.post_id
group by 1, 2, 3, 4

Or one array for each comment column:

select
    p.id, p.title, p.content, p.author,
    array_agg(c.author) as comment_author,
    array_agg(c.content) as comment_content
from
    posts p
    left join
    comments c on p.id = c.post_id
group by 1, 2, 3, 4

how to load 2 related datasets together? (i.e posts and comments)

1 Answers1