从SQL hasmany关系构造嵌套对象图 [英] Construct nested object graph from SQL hasmany relationship
本文介绍了从SQL hasmany关系构造嵌套对象图的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
示例:我有一些文章
和评论
,我想得到这样的东西:
Example: I have some articles
and comments
and I want to get something like this:
[{
title: "Article 1",
content: "Super long article goes here",
comments: [
{ author: "Troll", message: "You suck, Sir!" },
{ author: "SpamBot", message: "http://superawesomething.com/"}
]
},{
title: "Article 2",
content: "Another long article goes here",
comments: [ ... ]
}]
现在我看到两个解决方案:
Right now I see two solutions:
- 首先获取文章,然后在第二个查询中添加评论并具有一些
IN
条件,最后将注释添加到相应的文章中。 - 好的旧联接。对于我来说,我仍然不得不花很多时间处理数据才能进入我想要的结构。但除此之外,我有点担心,因为对于每个评论,都会传输诸如
articles.content
之类的有效负载-除非有一种我不知道的连接方式。
- Get the articles first, then the comments in a second query with some
IN
condition and finally add the comments to the respective articles. - Good old joins. For one I will still have to fiddle around with the data a lot to get into the structure I want. But beyond that I'm a little concerned since payload like
articles.content
will be transmitted for every comment - unless there is a way to do the join I am not aware of.
我希望我的SQL文盲让我错过了简单的解决方案。
I'm hoping that my SQL-illiteracy makes me miss the simple solution.
推荐答案
您可以使用聚合和/或子查询来做到这一点。像这样的东西:
You can do this, using aggregates and/or subqueries. Something like:
select title, content, json_agg(comments.author, comments.message) as comments
from articles
join comments on articles.article_id = comments.article_id
group by article_id;
如果您需要将其汇总成一个字符串/ json /某物-只需将其包装到另一个汇总查询中即可
If you need this aggregated into one string/json/something - just wrap it into another aggregate query like this:
select json_agg(sub)
from (
select title, content, json_agg(comments.author, comments.message) as comments
from articles
join comments on articles.article_id = comments.article_id
group by article_id) sub;
这是一个Postgres查询。对Mysql没有经验。
This is a Postgres query. Have no expirience with Mysql.
这篇关于从SQL hasmany关系构造嵌套对象图的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文