java构造方法嵌套,laravel查询构建器中的嵌套查询-CSDN博客

我需要根据请求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"

},{