MySQL查询从另一个表中检索相关数据

Question

我对SQL查询不太有经验(大多数时候我使用来自框架的查询生成器，如CodeIgniter和Laravel)。现在，我需要从一个关系数据库中检索数据，该数据库有两个表:一个用于实体条目，另一个用于实体的补充数据。例如:

tbl_posts

<表类> id 名称 蛞蝓 tbody><<tr>1Lorem ipsumLorem ipsum2测试文章testing-post3Hello worldHello world

代码没有显示在评论中，所以这里是供您检查的，抱歉我无法为理论辩论提供更多内容:

select 
tp.id,
tp.name,
tp.slug,
(select group_concat(`key`, "=",value separator ';' ) from tbl_posts_meta where post_id= tp.id )as datas
from tbl_posts tp

您可以按照以下简单步骤获取所需的json:

• 连接两个表以获得集中数据
• 通过使用GROUP_CONCAT聚合函数对感兴趣的列(tbl_posts.id、tbl_posts.name、tbl_posts.slug)进行聚合，生成一个类似tbl_posts_metajson的字符串。由于密钥可以是整数或字符串，因此需要使用CAST(<col> AS UNSIGNED)进行快速检查，如果字符串不是数字，则将返回0。
• 使用JSON_OBJECT函数将每一行转换为JSON
• 使用JSON_ARRAYAGG函数将所有json合并为一个json。

WITH cte AS(
SELECT p.id,
p.name, 
p.slug,
CONCAT('{',
GROUP_CONCAT(
CONCAT('"', 
m.key_, 
'": ', 
IF(CAST(m.value_ AS UNSIGNED)=0, 
CONCAT('"', m.value_, '"'),
CAST(m.value_ AS UNSIGNED))  ) 
ORDER BY m.id                             ),
'}') AS data_
FROM       tbl_posts      p
INNER JOIN tbl_posts_meta m
ON p.id = m.post_id
GROUP BY p.id,
p.name, 
p.slug
)
SELECT JSON_ARRAYAGG(
JSON_OBJECT("id",   id, 
"name", name, 
"slug", slug, 
"data", CAST(data_ AS JSON))) 
FROM cte

点击这里查看演示。

经过一些研究，测试和阅读你在这里发布的查询，我终于达到了我需要的结果。最后一个查询的结构如下:

SELECT *, (
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
key, value
)
) FROM tbl_posts_meta WHERE post_id = post.id
) AS data
FROM tbl_posts;

将其转换为Laravel的Eloquent查询构建器，并使用PHP的序列化/反序列化函数，最终结果如下所示:

$posts = Post::query()
->select("*")
->selectRaw('(SELECT JSON_ARRAYAGG(JSON_OBJECT(key, value)) FROM tbl_posts_meta WHERE post_id = posts.id) as data');
return $posts->get()->map(function($post){
$meta = [];
foreach($post->data as $dataItem){
foreach($dataItem as $key => $value){
$meta[$key] = unserialize($value);
}
}
$post->data = $meta;
return $post;
});

感谢您的支持和关注!:)