How to write this query in 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
I need it for Pagination. I can perform this query raw without any problems but the manually paginator gives always the same results: http://laravel.com/docs/5.1/pagination#manually-creating-a-paginator The same problem as here: http://laravel.io/forum/07-22-2015-laravel-51-manual-pagination-not-working-as-expected
My attempt without success:
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();
I think the problem is comments_count and status_sum? Thanks!
A subquery can be used with JOIN operation. In the example below, the subquery actually returns a temporary table which is handled by database server in memory. The temporary table from the subquery is given an alias so that we can refer to it in the outer select statement.
Whenever you need to use subquery in your laravel project you can use following example and learn hot to use subquery. In bellow example you can see we add subquery using DB::raw(), DB::raw() throught we can select our subtable and after in second argument i added compare your field.
Use a join or a subquery anytime that you reference information from multiple tables. Joins and subqueries are often used together in the same query. In many cases, you can solve a data retrieval problem by using a join, a subquery, or both.
To use subqueries with Laravel's query builder, you should add it to the join as follows:
->leftJoin(DB::raw("(SELECT [...]) AS p"), 'p.post_id', '=', 'posts.id')
It's also better to create an alias for calculated fields, as you did in your raw query:
COUNT(*) AS count
Despite this changes, unless I'm wrong, you can start by making your query simpler. Drop the subqueries, this way:
SELECT
p.id,
p.title,
p.created_at,
p.updated_at,
u.name,
COUNT(c.id) AS comments_count,
COALESCE(SUM(pl.status), 0) AS status_sum
FROM
posts p
LEFT OUTER JOIN
users u
ON
u.id = p.user_id
LEFT OUTER JOIN
postslikes pl
ON
pl.post_id = p.id
LEFT OUTER JOIN
comments c
ON
c.post_id = p.id
ORDER BY
comments_count DESC
GROUP BY
p.id
Then, with this new query, you can use Laravel to build it:
DB::table('posts')
->select([
'posts.id',
'posts.title',
'posts.created_at',
'posts.updated_at',
'users.name',
DB::raw('COUNT(comments.id) AS comments_count'),
DB::raw('COALESCE(SUM(postslikes.status), 0) AS status_sum'),
])
->leftJoin('users', 'users.id', '=', 'posts.user_id')
->leftJoin('comments', 'comments.post_id', '=', 'posts.id')
->leftJoin('postslikes', 'postslikes.post_id', '=', 'posts.id')
->orderBy('comments_count', 'DESC')
->groupBy('posts.id')
->get();
Note that I'm assuming you have a column named id
in your comments
table that is the primary key.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With