Laravel Eloquent 模型查询与自定义选择/加入/订单

Question

我有名为 Post 和 PostView 的模型。一个 Post 可以有多个 PostView。我需要获取按过去 30 天内创建的 PostViews 计数排序的分页帖子列表。

我可以这样做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);

但是，这会生成一个非常慢的查询，大约需要 24 秒。

使用原始 sql 我可以更有效地获得正确的结果，如何将其转换为分页模型集合？

这会生成正确的查询来获取前 10 个，但结果集合是空的。我认为这与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();

如果我运行直接在 mysql 中生成的查询，我会得到结果：（注意 - 为简洁起见，截断为 posts.id）

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)

任何帮助表示赞赏，谢谢。

Answer 1

查询应该是：$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) // no need of this            //->get(); // will be using paginate             ->paginate(10);

Answer 2

你有没有尝试过PostViews::where('created_at', '>=', 'DATE_ADD(CURDATE(), INTERVAL -30 DAY)')->groupBy('post_id')->selectRaw('count(*) as post_count')->with('post')->paginate(10);假设你有一个索引 created_at

Answer 3

我已经弄清楚了 - join->where 子句被转义并使查询无效。不是无效的sql，只是一个无效的比较，这样总是没有结果。当然，查询记录器并没有显示它被转义了，所以将查询复制到 mysql 中是可行的，这使得追踪起来比原本应该的要困难得多。这有效：$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', '>=', DB::raw('DATE_ADD(CURDATE(), INTERVAL -30 DAY)'));    })->groupBy('posts.id')->orderBy('views_count', 'DESC')重要的是：DB::raw('DATE_ADD(CURDATE(), INTERVAL -30 DAY)')