我需要根据请求ID获得1个帖子,结构如下:
postId;
postTitle;
postContent;
postImage;
bandName;
genreName;
标签:[tagId,tagName];
评论:[commentId,commentBody,commentCreatedAt] .
表结构:
个帖子(id,title,content,image,band_id,timestamps);
标签(id,name);
post_tag(post_id,tag_id);
comments(id,body,post_id,user_id,timestamps) .
我尝试了不同的查询变体,例如:
$post = DB::table('posts as p')
->select('p.id as postId',
'p.title as postTitle',
'p.content as postContent',
'p.image as postImage',
'b.name as bandName',
'g.name as genreName',
DB::raw("(SELECT t.id as tagId, t.name as tagName
FROM tags as t
JOIN post_tag as pt ON t.id = pt.tag_id
WHERE pt.post_id = $request->postId
GROUP BY tagId) as tags"))
->join('bands as b', 'b.id', 'p.band_id')
->join('genres as g', 'g.id', 'b.genre_id')
->where('p.id', $request->postId)
->groupBy(
'postId',
'postTitle',
'postContent',
'postImage',
'bandName',
'genreName')
->get();
但我卡住了标签((它返回错误:SQLSTATE [21000]:基数违规:1241操作数应该包含1列或其他 . )
如何获得帖子的标签(评论的查询将类似)?无法处理此类嵌套查询((我感谢任何帮助 .
Update 1.
尝试:
$post = DB::table('posts as p')
->select('p.id as postId',
'p.title as postTitle',
'p.content as postContent',
'p.image as postImage',
'b.name as bandName',
'g.name as genreName',
't.id as tagId',
't.name as tagName')
->join('post_tag as pt', 'p.id', 'pt.post_id')
->join('tags as t', 't.id', 'pt.tag_id')
->join('bands as b', 'b.id', 'p.band_id')
->join('genres as g', 'g.id', 'b.genre_id')
->where('p.id', $request->postId)
->groupBy(
'postId',
'postTitle',
'postContent',
'postImage',
'bandName',
'genreName',
'tagId')
->get();
结果:
[{
"postId",
"postTitle",
"postContent",
"postImage",
"bandName",
"genreName",
"tagId",
"tagName"
},{
"postId",
"postTitle",
"postContent",
"postImage",
"bandName",
"genreName",
"tagId",
"tagName"
}]
所以, "postId","postTitle","postContent","postImage","bandName","genreName" are duplicated((