Nested query from postgresql and pg-promise

Question

I am trying retrieve nested query from postgres in node with pg-promise.

I was only able to retrieve simple non nested result with mixed data from tables.

I want to get from three tables (users, posts, comments) one dataset for each post - users.username, posts.text, post.timestamp, comments.author, comments.text, comments.timestamp. Users and posts are linked with username, posts and comments are linked with posts.id and comments.parrent.

This is data I want to get:

[
 {"username": "jane", 
 "postText": "Hello", 
  "postId": 4,
  "postTimestamp: "1651156414", 
  allComments: [
       {"commentsId": 2,
        "commentsAuthor": "john", 
        "commentsText": "nice", 
        "commentsTimestamp": "156454565456"
        }, 
       {"commentsId": 3,
        "commentsAuthor": "ghost",
       "commentsText": "hiii", 
       "commentsTimestamp": "165165848"}]
   }]

I am doing it with pg-promise:

function getUsers(t) {
    return t.map('SELECT * FROM posts', post=> {
        return t.map('SELECT * FROM users WHERE users.username = post.username', post.id, user => {
        return t.any('SELECT * FROM comments, posts WHERE comments.parrent = post.id', user.id)
        .then(comment => {
              user.comment = comment;
                return user;
                });
        })
            .then(t.batch) 
            .then(user=> {
              post.user = user;
                return post;
            });
    }).then(t.batch); 
}

db.task(getUsers)
    .then(data => {
      console.log(data)
        res.send(data)
    })
    .catch(error => {
      console.log(error)
    });

});

It gives me error:

  TypeError: undefined is not a function at Array.map (<anonymous>) at obj.any.call.then.data

Do you have please any idea, what am I doing wrong? Thank you

Answer 1

The issue is on this line:

return t.map('SELECT * FROM posts', post => {

which should be:

return t.map('SELECT * FROM posts', [], post => {

You are passing in a function as a formatting parameter, hence the problem.

But generally, this post gives a far superior solution, in terms of the performance, via JSON function json_build_object, as you will execute just one query, versus many as you are trying.