php - MySQL Advanced加入laravel 5.2

我有三个表：帖子，评论和回复


回复表与发布表相同，但对发布的回复
帖子表有评论
回复表也有评论
评论表用于存储回复表和帖子表的评论
我希望得到所有有评论的帖子，即使帖子的回复有评论，那么也包括该帖子，并且结果帖子应该是唯一的...


这就是我尝试过的：

$RC = DB::table('comments')
    ->join('replies','comments.reply_id','=','replies.id')
    ->where('comments.handle',$request->handle)
    ->where('replies.handle','!=',$request->handle)
    ->groupBy('replies.post_id')
    ->get(['replies.post_id']);

$PC = DB::table('comments')
    ->join('posts', 'comments.post_id', '=', 'posts.id')
    ->where('comments.handle',$request->handle)
    ->where('posts.handle','!=',$request->handle)
    ->groupBy('comments.post_id')
    ->get(['comments.post_id']);

最佳答案

最后，我正在使用此代码来获得所需的结果

$res = DB::select("SELECT posts.* FROM posts JOIN replies ON posts.id = replies.post_id
                    JOIN comments ON posts.id = comments.post_id OR replies.id = comments.reply_id
                    WHERE comments.handle = $request->handle AND posts.handle != $request->handle
                    GROUP BY posts.id");

$res1 = DB::table('posts')
    ->join('comments',function ($join){
        $join->on('posts.id','=','comments.post_id')
            ->where('comments.handle','=',$request->handle)
            ->Where('posts.handle','<>',$request->handle);
    })
    ->groupBy('posts.id')
    ->get(['posts.*']);
$results = array_merge($res,$res1);
$results= collect($results);
$results = $results->unique();

关于php - MySQL Advanced加入laravel 5.2，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46053872/