带有附加 where 子句的 laravel withsum

Question

我知道您可以对这样的关系执行 withSum:

$posts = Post::withSum('comments', 'votes')->get();

但我喜欢在其上附加一个 where 子句，像这样(不起作用，但作为示例):

$posts = Post::withSum('comments', 'votes', function (Builder $query) {
                $query->where('comments.votes', '>', 5);
            })->get()

这可能吗？

Answer 1

同时 Aggregating Related Models在文档中没有明确说明这一点，它确实通过提及给了我们一个线索:

If you need to set additional query constraints on the count query, you may pass an array keyed by the relationships you wish to count. The array values should be closures which receive the query builder instance.

If you need to set additional query constraints on the count query, you can pass an encoded array by the relationships you want to count. The values in the array must be anonymous functions that receive the query builder instance.

因此，我们可以利用该匿名函数来修改查询生成器实例，而不是采用默认值(这将使用聚合“count”)，并将我们想要执行的子查询传递给它，即where 子句和列的“总和”。

也就是说，您的查询看起来像:

Post::withCount(['comments as comments_sum_votes' => function($query) {
    $query->where('comments.votes', '>', 5)->select(DB::raw('sum(votes)'));
}])->get()