Laravel+PostgreSQL环境下特定SQL转Eloquent查询求助

嘿，我来帮你搞定这个转换！首先咱们先把模型关联理清楚，然后一步步把你的SQL转成优雅的Eloquent查询。

第一步：定义模型关联 #

首先要确保你的Extension和XmlCdr模型之间建立了正确的关联关系——毕竟一个分机（extension）对应多条通话记录（xml_cdr）。

在Extension模型里添加关联：

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

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Extension extends Model
{
    protected $table = 'v_extensions'; // 明确指定表名
    protected $primaryKey = 'extension_uuid'; // 因为你的表主键是extension_uuid而非默认的id

    // 定义与XmlCdr的一对多关联
    public function xmlCdrs(): HasMany
    {
        // 参数：关联模型，关联外键，当前模型的关联字段
        return $this->hasMany(XmlCdr::class, 'extension_uuid', 'extension_uuid');
    }
}

对应的XmlCdr模型可以添加反向关联（可选，但推荐）：

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

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class XmlCdr extends Model
{
    protected $table = 'v_xml_cdr';
    protected $primaryKey = 'uuid';

    public function extension(): BelongsTo
    {
        return $this->belongsTo(Extension::class, 'extension_uuid', 'extension_uuid');
    }
}

第二步：转换SQL为Eloquent查询 #

你的原SQL是查询分机号，同时统计该分机在指定日期范围内的通话次数，然后按次数降序取第一条。这里有两种简洁的实现方式：

方式一：使用withCount（最推荐） #

Laravel的withCount方法专门用来统计关联模型的数量，还能直接添加过滤条件，比手动写子查询更简洁安全：

use App\Models\Extension;
use Carbon\Carbon;

// 替换成你的实际日期参数，用Carbon处理更方便
$startDate = Carbon::parse('2024-01-01');
$endDate = Carbon::parse('2024-01-31');

$topExtension = Extension::select('extension') // 只选需要的字段
    ->withCount(['xmlCdrs' => function ($query) use ($startDate, $endDate) {
        // 给关联统计添加日期范围过滤
        $query->where('start_stamp', '>=', $startDate)
              ->where('end_stamp', '<=', $endDate);
    }])
    ->orderByDesc('xml_cdrs_count') // 按统计次数降序
    ->first(); // 取第一条

// 访问结果
echo "通话最多的分机：{$topExtension->extension}，次数：{$topExtension->xml_cdrs_count}";

方式二：手动子查询（贴近原SQL结构） #

如果你想更贴近原SQL的子查询写法，可以用selectSub方法：

use App\Models\Extension;
use Carbon\Carbon;

$startDate = Carbon::parse('2024-01-01');
$endDate = Carbon::parse('2024-01-31');

$topExtension = Extension::select('extension')
    ->selectSub(function ($subQuery) use ($startDate, $endDate) {
        $subQuery->from('v_xml_cdr')
            ->whereColumn('v_xml_cdr.extension_uuid', 'v_extensions.extension_uuid')
            ->where('start_stamp', '>=', $startDate)
            ->where('end_stamp', '<=', $endDate)
            ->selectRaw('COUNT(uuid)');
    }, 'count') // 给子查询结果起别名count
    ->orderByDesc('count')
    ->first();

// 访问结果
echo "通话最多的分机：{$topExtension->extension}，次数：{$topExtension->count}";

关键说明 #

• 用whereColumn来关联两个表的字段，避免硬写字段值，确保关联逻辑正确。
• 使用Carbon处理日期可以让Laravel自动处理日期格式，同时防止SQL注入风险。
• withCount方法会自动生成和原SQL一致的子查询，而且代码更易读维护。

内容的提问来源于stack exchange，提问作者Ertan Hasani