mysql 多次左连接 sum_mysql – 如何在Laravel 5.1中编写这个(左连接,子查询)？

如何在Laravel 5.1中编写此查询：

SELECT p.id, p.title, p.created_at, p.updated_at, u.name, COALESCE(c.comments_count, 0) AS comments_count, COALESCE(pl.status_sum, 0) AS status_sum

FROM posts p

LEFT OUTER JOIN users u ON u.id = p.user_id

LEFT OUTER JOIN (

SELECT pl.post_id, SUM(pl.status) AS status_sum

FROM postslikes pl

GROUP BY pl.post_id

) pl ON pl.post_id = p.id

LEFT OUTER JOIN (

SELECT c.post_id, COUNT(*) as comments_count

FROM comments c

GROUP BY c.post_id

) c ON c.post_id = p.id ORDER BY comments_count DESC

我没有成功的尝试：

DB::table( 'posts' )

->select( 'posts.id', 'posts.title', 'posts.created_at', 'posts.updated_at', 'users.name', DB::raw( 'COALESCE( comments.body, 0 ), COALESCE( postslikes.status, 0 )' ) )

->leftJoin( 'users', 'users.id', '=', 'posts.user_id' )

->leftJoin( DB::raw( 'SELECT postslikes.post_id, SUM( postslikes.status ) FROM postslikes GROUP BY postslikes.post_id' ), function( $join )

{

$join->on( 'postslikes.post_id', '=', 'post.id' );

})

->leftJoin( DB::raw( 'SELECT comments.post_id, COUNT(*) FROM comments GROUP BY comments.post_id' ), function( $join )

{

$join->on( 'comments.post_id', '=', 'post.id' );

})

->get();

我认为问题是comments_count和status_sum？

谢谢！