Combine nested loop queries to parent array result - pg-promise

Question

I'm new to node(express) and pg-promise, and have not been able to figure out how to add the result of each nested query(loop) into the main json array result query.

I have two tables: Posts and comments.

CREATE TABLE post(
id serial,
content text not null,
linkExterno text,
usuario VARCHAR(50) NOT NULL REFERENCES usuarios(alias) ON UPDATE cascade ON DELETE cascade,
multimedia text,
ubicacation VARCHAR(100),
likes integer default 0,
time VARCHAR default now(),
reported boolean default false,
PRIMARY KEY (id)  );

CREATE TABLE comment(
id serial,
idPost integer NOT NULL REFERENCES post(id) ON UPDATE cascade ON DELETE cascade,
acount VARCHAR(50) NOT NULL REFERENCES users(alias) ON UPDATE cascade ON DELETE cascade,
content text NOT NULL,
date date default now(),
PRIMARY KEY (id));

---

So I want to add the result of each comments to each post and return the posts. I have this, but doesn't work:

con.task(t => {
    return t.any('select *, avatar from post, users where user= $1 and user = alias ORDER BY time DESC LIMIT 10 OFFSET $2', [username, pos])
    .then(posts => {
        if(posts.length > 0){
            for (var post of posts){
                post.coments = t.any('select * from comment where idPost = $1 ', post.id);
            }
        }
    });
}).then(posts => {
    res.send(posts);
}).catch(error => {
    console.log(error);
});

Any suggestions? PD: I think my question is kind of similar to this one: get JOIN table as array of results with PostgreSQL/NodeJS

ANSWERS:

Option 1 (best choice):

Making a single query through JSON to psql (JSON query)

See answer by @vitaly-t

OR

Getting the nested data asynchronously using ajax.

Option 2:

function buildTree(t) {
        return t.map("select *, avatar from publicacion, usuarios where usuario = $1 and usuario = alias ORDER BY hora DESC LIMIT 10 OFFSET $2", [username, cantidad], posts => {
                return t.any('select * from comentario where idPublicacion = $1', posts.id)
                    .then(coments => {
                        posts.coments = coments;
                        console.log(posts.coments);
                        return posts;
                    });
        }).then(t.batch); // settles the array of generated promises
    }

    router.get('/publicaciones', function (req, res) {
        cantidad = req.query.cantidad || 0; //num de publicaciones que hay
        username = req.session.user.alias;

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

Option 3(async):

try{
    var posts = await con.any('select *, avatar from post, users where user = $1 and user = alias ORDER BY time DESC LIMIT 10 OFFSET $2', [username, q])
    for (var post of posts){
        post.coments = await con.any('select * from comment where idPublictcion = $1', post.id);
    }
}catch(e){
    console.log(e);
}

Synge

Actually, the best answer is the second option through a JSON query here: #39806236, because it will be hugely faster than anything else, and will scale well. And that await code will be the worst in terms of scaling, as it is synchronous ;) – Isodynamic

@Isodynamic Your are rigth! Thanks for your time! :) – Synge

See the update, you might like it better ;) – Isodynamic

Answer 1

I'm the author of pg-promise ;)

---

con.task(t => {
    const a = post => t.any('SELECT * FROM comment WHERE idPost = $1', post.id)
        .then(comments => {
            post.comments = comments;
            return post;
        });
    return t.map('SELECT *, avatar FROM post, users WHERE user = $1 AND user = alias ORDER BY time DESC LIMIT 10 OFFSET $2', [username, pos], a)
        .then(t.batch);
})
    .then(posts => {
        res.send(posts);
    })
    .catch(error => {
        console.log(error);
    });

Also see this question: get JOIN table as array of results with PostgreSQL/NodeJS.

UPDATE

In case you do not want to go all the way with the JSON query approach, then the following will scale much better than the original solution, as we concatenate all child queries, and then execute them as one query:

con.task(async t => {
    const posts = await t.any('SELECT *, avatar FROM post, users WHERE user = $1 AND user = alias ORDER BY time DESC LIMIT 10 OFFSET $2', [username, pos]);
    const a = post => ({query: 'SELECT * FROM comment WHERE idPost = ${id}', values: post});
    const queries = pgp.helpers.concat(posts.map(a));
    await t.multi(queries)
        .then(comments => {
            posts.forEach((p, index) => {
                p.comments = comments[index];
            });
        });
    return posts;
})
    .then(posts => {
        res.send(posts);
    })
    .catch(error => {
        console.log(error);
    });

See API:

• helpers.concat
• Database.multi

Isodynamic

Nice update! The problem is that I don't have the pgp object in my route file, just the conection instance but anyway good contribution. – Synge

@SergioRey That doesn't sound right. If you organize the library the way you should, you will not have that problem. – Isodynamic

How can this be implemented when you have to combine more than two queries? – Furness

@NaveenGeorgeThoppan That depends entirely on the query logic/relationship. Cannot answer it in a generic way. – Isodynamic

In the same case as above, a post can have votes associated with it. How can I add a query for getting the votes as well for the post? So the there will be two queries to get comments and votes. Can you help me with the syntax to add another query? In my case I've 5 or 6 such queries to be added. – Furness

@NaveenGeorgeThoppan You can either use the same map + batch logic as in the original answer, or use the same approach (in update). I cant post a solution in the comments. – Isodynamic

@Isodynamic I'll post a question. Hope you'll be able to help me. – Furness

@Isodynamic Please see question #51476972 – Furness

@Isodynamic you my friend are a legend, this example is exactly what I was looking for (found it in half a day), can you please also put this one into. your section of Learn By Example for lesser mortals :) , that one is good for single value return for parent query, but i had to scourge the net for an example where multiple were returned and then populated. – Irina

Answer 2

If you want structured (nested) data, without having to

A) re-write your sql using json function, or split it out into multiple task queries, or

B) refactor your code to use the API of a heavy ORM

you could check out sql-toolkit. It's a node library built for pg-promise which allows you to write regular native SQL and receive back properly structured (nested) pure business objects. It's strictly an enhancement toolkit on top of pg-promise, and does not seek to abstract out pg-promise (you still set up pg-promise and can use it directly).

For example:

class Article extends BaseDAO {
  getBySlug(slug) {
    const query = `
      SELECT
        ${Article.getSQLSelectClause()},
        ${Person.getSQLSelectClause()},
        ${ArticleTag.getSQLSelectClause()},
        ${Tag.getSQLSelectClause()}
      FROM article
      JOIN person
        ON article.author_id = person.id
      LEFT JOIN article_tags
        ON article.id = article_tags.article_id
      LEFT JOIN tag
        ON article_tags.tag_id = tag.id
      WHERE article.slug = $(slug);
  `;
  return this.one(query, { slug });
  // OUTPUT: Article {person: Person, tags: Tags[Tag, Tag, Tag]}
}

The select clause uses the business object "getSQLSelectClause" methods to save tedium in typing the columns, as well as ensure no collisions of names (nothing magical going on, and could just be written out instead).

The this.one is a call into sql-toolkits base DAO class. It is responsible for structuring the flat result records into a nice nested structure.

(Also notice that it is "one" which matches our mental model for the SQL. The DAO methods for one, oneOrNone, many, and any ensure their count against the number of generated top level business objects - not the number of rows the sql expression returns!)

Check out the repository for details on how to set it up on top of pg-promise. (Disclamer, I am the author of sql-toolkit.)

Childish

Answer 3

You can use await but it will work sync.

return t.any('select *, avatar from post, users where user= $1 and user = alias ORDER BY time DESC LIMIT 10 OFFSET $2', [username, pos])
    .then(posts => {
        if(posts.length > 0){
            for (var post of posts){
                post.coments = await t.any('select * from comment where idPost = ', post.id);
            }
        }
        return posts;
    });

Actually i recommend you to use orm tools like bookshelf, knex, typeorm

Astra

The solution with pg-promise will outperform any of the mentioned ORM-s by a large margin, so it is not a good recommendation. – Isodynamic

Thanks for your great module vitaly-t ;) – Synge