Eloquent Model query with custom select/join/order

I think you have to do your select like so

Post::select('posts.*', DB::raw('count(post_views.id) as views_count'))

After that you can create your own custom pagination object: https://laravel.com/docs/5.8/pagination#manually-creating-a-paginator

Let me know if you need an example for the LengthAwarePaginator class ;)

Thanks for the reply

I tried that like so:

$posts = Post::select('posts.*', DB::raw('count(post_views.id) as views_count'))
    ->join('post_views', function($join) {
        $join->on('posts.id', '=', 'post_views.post_id')
            ->where('post_views.created_at', '>=', 'DATE_ADD(CURDATE(), INTERVAL -30 DAY)');
        })
    ->groupBy('posts.id')
    ->orderBy('views_count', 'DESC')
    ->take(10)
    ->get();

    // ideally ->paginate(10) instead of take/get

That generates this query:

select `posts`.*, count(post_views.id) as views_count from `posts` inner join `post_views` on `posts`.`id` = `post_views`.`post_id` and `post_views`.`created_at` >= DATE_ADD(CURDATE(), INTERVAL -30 DAY) group by `posts`.`id` order by `views_count` desc limit 10

Which gives the same result as in the original question, but I still end with $posts being:

Illuminate\Database\Eloquent\Collection Object
(
    [items:protected] => Array
        (
        )

)

If there's something with a custom paginator that would do it please point me there, my confusion is that the query has results, but the collection doesn't contain the models.

Through a very time consuming method of trial & error and selective deleting things, including reducing the query to a DB::table() ignoring the model I have found that issue is somewhere in the JOIN

I don't know what yet though, as the query it produces seems valid, and when run in mysql directly has the correct results. However the join + where together creates no results. As far as I can tell the structure of it is in line with the docs for using a join + where

@bobbybouwmann - you were so close, and I completely missed it, but it's not just the select that requires the DB::raw treatment...

$join->on('posts.id', '=', 'post_views.post_id')
    ->where('post_views.created_at', '>=', 'DATE_ADD(CURDATE(), INTERVAL -30 DAY)');

The DATE_ADD() and CURDATE() were getting escaped, and of course in the query log you can't tell it's been escaped so the query log copied into mysql directly worked...

/facepalm

So... this did the trick

$join->on('posts.id', '=', 'post_views.post_id')
    ->where('post_views.created_at', '>=', DB::raw('DATE_ADD(CURDATE(), INTERVAL -30 DAY)'));

Thanks for the help!

Aah yeah that makes sense! Glad you fixed it ;)