Laravel Eloquent Model query with custom select/join/order

I have models called Post, and PostView. A Post can have many PostViews. I need to get a paginated list of Posts ordered by the count of PostViews created within the last 30 days.

I can do this using withCount:

// works but S L O W
$posts = Post::withCount(['views' => function($query) {
            $query->where('post_views.created_at', '>=', 'DATE_ADD(CURDATE(), INTERVAL -30 DAY)');
         }])
         ->orderBy('views_count')
         ->paginate(10);

However this generates a query that is really slow, taking ~24 seconds.

Using raw sql I can get the correct results much more efficiently, how can I turn that into the paginated model collection?

This generates the correct query to grab the first 10, but the resulting collection is empty. I assume it has something to do with the selectRaw

$posts = Post::selectRaw('posts.*, 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();

If I run the query that generates directly in mysql I do get results: (note - truncated to posts.id for brevity)

mysql> select posts.id, 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;
+--------+-------------+
| id     | views_count |
+--------+-------------+
| 150277 |          22 |
|  43843 |           6 |
| 138789 |           4 |
| 180565 |           4 |
|  50555 |           3 |
|   2679 |           3 |
| 188572 |           3 |
| 217454 |           3 |
| 136736 |           3 |
| 126472 |           2 |
+--------+-------------+
10 rows in set (1.26 sec)

Any help is appreciated, thanks.

via Chebli Mohamed