将嵌套循环查询合并到父数组结果-pg-promise [英] Combine nested loop queries to parent array result - pg-promise

问题描述

我是node（express）和pg-promise的新手，还无法弄清楚如何将每个嵌套查询（循环）的结果添加到主json数组结果查询中。

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);
});

有什么建议吗？
PD：我认为我的问题与此类似：
使用PostgreSQL / NodeJS

答案：

选项1（最佳选择）：

制作一个通过JSON查询到psql（ JSON查询）

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

请参见@ vitaly-t的答案

See answer by @vitaly-t

OR

OR

使用ajax异步获取嵌套数据。

Getting the nested data asynchronously using ajax.

选项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);
        });
    });

选项3（异步）：

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);
}

推荐答案

我是 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);
    });

也请参见以下问题：使用PostgreSQL / NodeJS将JOIN表作为结果数组获取。

更新

如果您不想一直使用JSON查询方法，那么以下内容会更好地扩展与原始解决方案相比，我们连接了所有子查询，然后将它们作为一个查询执行：

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);
    });

请参阅API：

• helpers.concat


• Database.multi

• helpers.concat
• Database.multi

这篇关于将嵌套循环查询合并到父数组结果-pg-promise的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！