php - Laravel 5.2 中的 Mysql 高级联接

Question

我有三个表:帖子、评论和回复

• 回复表与帖子表相同，但它是对帖子的回复
• 帖子表有评论
• 回复表也有评论
• 评论表用于存储replies表和posts表的评论
• 我希望获得所有有评论的帖子，即使帖子的回复有评论，然后也包含该帖子，并且生成的帖子应该是唯一的...

这是我尝试过的::

$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']);

Answer 1

最后我使用这段代码来获得想要的结果

$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();