php - 仅 Mysql 原始计数

Question

我在我的项目中使用 PHP 和 Laravel 框架。

以上代码完美运行

$users = User::select([
            'users.id',
            'users.name',
            'users.company',
            'users.country',
            'users.city',
            'users.email',
            'users.created_at',
            \DB::raw('SUM(reservations.dolar) as dolar'),
            \DB::raw('count(reservations.confirmation) as confirmation'),
        ])->join('reservations','reservations.user_id','=','users.id')
        ->groupBy('reservations.user_id');

现在计算所有 reservation.confirmation 列，但我只想计算 reservation.confirmation 列值 1

如何编辑

\DB::raw('count(reservations.confirmation) as confirmation'),

这段代码

Answer 1

你试过看https://laravel.com/docs/5.6/queries#joins吗在“Advanced Join Clauses”部分，您可以在Join函数中添加条件

$users = User::select([
        'users.id',
        'users.name',
        'users.company',
        'users.country',
        'users.city',
        'users.email',
        'users.created_at',
        \DB::raw('SUM(reservations.dolar) as dolar'),
        \DB::raw('count(reservations.confirmation) as confirmation'),
    ])->join('reservations', function ($join) {
                              $join->on('reservations.user_id', '=', 'users.id')
                                   ->where('reservations.confirmation', '=', 1);
    })->groupBy('reservations.user_id');