Laravel集合where LIKE查询失效问题及替代方案咨询

核心问题分析 #

你在集合中使用where('kode_rekening', 'LIKE', '%5.1.03%')无效的原因是：Laravel集合的where方法不支持SQL的LIKE操作符，它仅支持PHP原生的比较运算符（如=、!=、<等）。同时你的代码还存在逻辑组合错误（比如多个where叠加的AND逻辑不符合需求）、查询效率低下（大量pluck/flatten遍历）等问题。

替代过滤方法 #

针对匹配kode_rekening前7位的需求，推荐以下两种方案：

方案1：使用Laravel 9+的whereLike方法 #

Laravel 9及以上版本为集合新增了whereLike方法，直接支持模糊匹配：

// 匹配以5.1.03开头的字符串（精准匹配前7位逻辑）
->whereLike('kode_rekening', '5.1.03%')

方案2：使用filter闭包自定义匹配逻辑 #

如果是低版本Laravel，或需要更灵活的判断，用filter结合字符串函数实现前缀匹配：

// 匹配前7位为5.1.03的字符串
->filter(function ($item) {
    // 若kode_rekening格式为5.1.03.xxxxx，前7位是"5.1.03."则用substr($item->kode_rekening, 0, 7) === '5.1.03.'
    return str_starts_with($item->kode_rekening, '5.1.03');
})

代码优化要点 #

1. 修复逻辑组合错误 #

原代码中多个where('kode_rekening', 'LIKE', ...)是AND关系，不可能同时匹配多个不同前缀，需改为OR逻辑：

// 替换原多where的AND逻辑
->filter(function ($rek) {
    $targetPrefixes = ['5.1.03', '5.1.04', '5.1.05', '5.1.06', '5.2.01'];
    foreach ($targetPrefixes as $prefix) {
        if (str_starts_with($rek->kode_rekening, $prefix)) {
            return true;
        }
    }
    return false;
})

2. 修复查询构建器的orWhere冲突 #

原$sub查询中的where('induk_id', $item->id)->orWhere('id', $item->id)会和前面的when条件冲突，需用闭包包裹：

->where(fn($q) => $q->where('induk_id', $item->id)->orWhere('id', $item->id))

3. 减少无效集合操作 #

提取通用的rekening集合，避免重复的pluck/flatten操作，提升效率：

$allRekenings = $sub->pluck('program')->flatten()->pluck('kegiatan')->flatten()->pluck('subKegiatan')->flatten()->pluck('rekening')->flatten();

优化后的完整代码片段 #

$data = Unit::with([
    'program.kegiatan.subKegiatan.rekening' => fn ($query) => $query->where('level', 9),
    'penyedia',
    'swakelola',
])
->whereNull('induk_id')
->orderBy('kode', 'desc')
->get()->transform(function ($item) use ($filters) {
    // 修复sub查询的orWhere逻辑
    $sub = Unit::with([
        'program.kegiatan.subKegiatan.rekening' => fn ($query) => $query->where('level', 9),
        'penyedia',
        'swakelola',
    ])
    ->orderBy('kode', 'desc')
    ->when($filters == 'false', fn ($query) => $query->where('induk_id', NULL))
    ->where(fn($q) => $q->where('induk_id', $item->id)->orWhere('id', $item->id))
    ->get();

    // 提取通用的rekening集合，避免重复遍历
    $allRekenings = $sub->pluck('program')->flatten()->pluck('kegiatan')->flatten()->pluck('subKegiatan')->flatten()->pluck('rekening')->flatten();

    $excludePrefixes = ['5.1.03', '5.1.04', '5.1.05', '5.1.06', '5.2.01'];
    $includeNpPrefixes = ['5.1.03', '5.1.04', '5.1.05', '5.1.06', '5.2.01'];

    return [
        'id' => $item?->uuid,
        'skpd_id' => $item?->id,
        'nama' => $item?->nama,
        'kode' => $item?->kode,
        'filter' => $filters,
        'pagu' => $allRekenings->sum('jumlah'),
        // IDEN APBD
        'iden_p_apbd' => $allRekenings->filter(function ($rek) use ($excludePrefixes) {
            foreach ($excludePrefixes as $prefix) {
                if (str_starts_with($rek->kode_rekening, $prefix)) {
                    return false;
                }
            }
            return true;
        })->sum('jumlah'),
        'iden_np_apbd' => $allRekenings->where('level', '9')
            ->filter(function ($rek) use ($includeNpPrefixes) {
                foreach ($includeNpPrefixes as $prefix) {
                    if (str_starts_with($rek->kode_rekening, $prefix)) {
                        return true;
                    }
                }
                return false;
            })->sum('jumlah'),
        'iden_g_apbd' => $allRekenings->filter(fn($rek) => str_starts_with($rek->kode_rekening, '5.1.01'))->sum('jumlah'),
        // IDEN PAKET
        'iden_p' => $allRekenings->where('metode', 'p')->sum('jumlah'),
        'iden_s' => $allRekenings->where('metode', 's')->sum('jumlah'),
        'iden_np' => $allRekenings->where('metode', 'np')->sum('jumlah'),
        'iden_g' => $allRekenings->where('metode', 'g')->sum('jumlah'),
        'iden_noiden' => $allRekenings->where('metode', '')->sum('jumlah'),
        'total_iden' => $allRekenings->whereIn('metode', ['p', 's', 'np', 'g'])->sum('jumlah'),

        // sub skpd (upt)
        'sub' => $sub->transform(fn ($upt) => [
            'id' => $upt?->id,
            'induk_id' => $upt?->induk_id,
            'nama' => $upt?->nama,
            'kode' => $upt?->kode,
            'filter' => $filters,
            'pagu' => $upt->program->flatten()->pluck('kegiatan')->flatten()->pluck('subKegiatan')->flatten()->pluck('rekening')->flatten()->sum('jumlah'),
            // IDEN APBD
            'iden_p_apbd' => $upt->program->flatten()->pluck('kegiatan')->flatten()->pluck('subKegiatan')->flatten()->pluck('rekening')->flatten()
                ->filter(function ($rek) use ($excludePrefixes) {
                    return $rek->metode === 'np' || !collect($excludePrefixes)->contains(fn($p) => str_starts_with($rek->kode_rekening, $p));
                })->sum('jumlah'),
            'iden_np_apbd' => $upt->program->flatten()->pluck('kegiatan')->flatten()->pluck('subKegiatan')->flatten()->pluck('rekening')->flatten()
                ->where('level', '9')
                ->filter(function ($rek) use ($includeNpPrefixes) {
                    foreach ($includeNpPrefixes as $prefix) {
                        if (str_starts_with($rek->kode_rekening, $prefix)) {
                            return true;
                        }
                    }
                    return false;
                })->sum('jumlah'),
            'iden_g_apbd' => $upt->program->flatten()->pluck('kegiatan')->flatten()->pluck('subKegiatan')->flatten()->pluck('rekening')->flatten()
                ->filter(fn($rek) => str_starts_with($rek->kode_rekening, '5.1.01'))->sum('jumlah'),
            // IDEN PAKET
            'iden_p' => $upt->program->flatten()->pluck('kegiatan')->flatten()->pluck('subKegiatan')->flatten()->pluck('rekening')->flatten()->where('metode', 'p')->sum('jumlah'),
            'iden_s' => $upt->program->flatten()->pluck('kegiatan')->flatten()->pluck('subKegiatan')->flatten()->pluck('rekening')->flatten()->where('metode', 's')->sum('jumlah'),
            'iden_np' => $upt->program->flatten()->pluck('kegiatan')->flatten()->pluck('subKegiatan')->flatten()->pluck('rekening')->flatten()->where('metode', 'np')->sum('jumlah'),
            'iden_g' => $upt->program->flatten()->pluck('kegiatan')->flatten()->pluck('subKegiatan')->flatten()->pluck('rekening')->flatten()->where('metode', 'g')->sum('jumlah'),
            'iden_noiden' => $upt->program->flatten()->pluck('kegiatan')->flatten()->pluck('subKegiatan')->flatten()->pluck('rekening')->flatten()->where('metode', '')->sum('jumlah'),
            'total_iden' => $upt->program->flatten()->pluck('kegiatan')->flatten()->pluck('subKegiatan')->flatten()->pluck('rekening')->flatten()->whereIn('metode', ['p', 's', 'np', 'g'])->sum('jumlah')
        ])->sortBy('kode')->where('pagu', '!=', 0)->values(),
    ];
})->sortBy('kode')->where('pagu', '!=', 0)->values();

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