Laravel中如何用Eloquent实现contact表stats字段按周/月/季度多维度求和

实现方案 #

完全可以通过Eloquent实现，不需要强制使用DB facade，且Eloquent实现的可维护性远高于手写原生SQL。

前置准备 #

首先你需要创建对应contact表的Eloquent模型，示例如下：

// app/Models/Contact.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Contact extends Model
{
    protected $table = 'contact'; // 绑定你的表名
    public $timestamps = false; // 表无created_at/updated_at字段时添加此行
    protected $fillable = ['client_id', 'room_id', 'stats', 'date'];
}

适配原需求的基础查询 #

以下代码和你提供的原生SQL逻辑完全等价，同时将公共过滤条件抽离，代码更简洁：

use App\Models\Contact;

$stats = Contact::selectRaw("
    SUM(CASE WHEN date > '2021-01-11 00:00:00' AND date < '2021-01-18 00:00:00' THEN stats ELSE 0 END) as week1,
    SUM(CASE WHEN date > '2021-12-20 00:00:00' AND date < '2021-12-27 00:00:00' THEN stats ELSE 0 END) as week51,
    SUM(CASE WHEN date BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:59' THEN stats ELSE 0 END) as month1,
    SUM(CASE WHEN date BETWEEN '2021-12-01 00:00:00' AND '2021-12-31 23:59:59' THEN stats ELSE 0 END) as month12
")
->where('room_id', 6)
->where('client_id', 1)
->first();

更灵活的全维度批量查询 #

如果需要统计指定年份的所有周、月、季度数据，可以动态生成查询字段，无需手动拼写每一段SQL：

use App\Models\Contact;

// 可自定义配置参数
$targetYear = 2021;
$clientId = 1;
$roomId = 6;

$selectFields = [];

// 批量生成12个月的统计字段
for ($month = 1; $month <=12; $month++) {
    $monthStart = date('Y-m-d 00:00:00', strtotime("{$targetYear}-{$month}-01"));
    $monthEnd = date('Y-m-t 23:59:59', strtotime("{$targetYear}-{$month}-01"));
    $selectFields[] = "SUM(CASE WHEN date BETWEEN '{$monthStart}' AND '{$monthEnd}' THEN stats ELSE 0 END) as month{$month}";
}

// 批量生成年度所有周的统计字段（自动过滤跨年无效周）
for ($week =1; $week <=53; $week++) {
    $weekStart = date('Y-m-d 00:00:00', strtotime("{$targetYear}-W{$week}-1"));
    $weekEnd = date('Y-m-d 23:59:59', strtotime("{$targetYear}-W{$week}-7"));
    if (date('Y', strtotime($weekStart)) == $targetYear) {
        $selectFields[] = "SUM(CASE WHEN date BETWEEN '{$weekStart}' AND '{$weekEnd}' THEN stats ELSE 0 END) as week{$week}";
    }
}

// 批量生成4个季度的统计字段
$quarterRanges = [
    1 => ['01-01', '03-31'],
    2 => ['04-01', '06-30'],
    3 => ['07-01', '09-30'],
    4 => ['10-01', '12-31'],
];
foreach ($quarterRanges as $q => $range) {
    $qStart = "{$targetYear}-{$range[0]} 00:00:00";
    $qEnd = "{$targetYear}-{$range[1]} 23:59:59";
    $selectFields[] = "SUM(CASE WHEN date BETWEEN '{$qStart}' AND '{$qEnd}' THEN stats ELSE 0 END) as quarter{$q}";
}

// 执行查询
$yearStats = Contact::selectRaw(implode(',', $selectFields))
    ->where('client_id', $clientId)
    ->where('room_id', $roomId)
    ->whereYear('date', $targetYear)
    ->first();

扩展说明 #

如果后续需要按多客户端、多房间分组统计，仅需在查询末尾添加->groupBy('client_id', 'room_id')即可，扩展性远高于手写原生SQL。

内容的提问来源于stack exchange，提问作者Alexandru Popa