解决Rappasoft Laravel Livewire Datatables工单工作项计数列排序问题

1. 先解决「Unknown column 'jobs.w_i'」报错 #

报错的核心原因是你的WI列配置中指定了不存在的字段w_i，需要修正列定义，匹配子查询生成的别名wi_count：

public function columns(): array
{
    return [
        // 其他列配置...
        Column::make('WI', 'wi_count')
            ->sortable()
            ->format(fn($count) => $count),
        // 其他列配置...
    ];
}

2. 修复子查询字段的排序逻辑 #

修正列名后，还需要处理子查询别名的排序兼容问题，修改builder()方法，根据datatable的排序参数动态处理：

public function builder(): Builder
{
    return Job::query()
        ->select(['*', DB::raw('(SELECT COUNT(*) FROM job_work_items WHERE job_number = jobs.number) as wi_count')])
        ->where('is_draft', 0)
        ->where('redirect', 0)
        ->with('client')
        // 替换固定orderBy，根据排序参数动态处理
        ->when($this->getSortColumn(), function ($query, $column) {
            if ($column === 'wi_count') {
                // 针对子查询字段，直接用子查询语句作为排序依据
                $query->orderBy(DB::raw('(SELECT COUNT(*) FROM job_work_items WHERE job_number = jobs.number)'), $this->getSortDirection());
            } else {
                // 其他字段正常排序
                $query->orderBy($column, $this->getSortDirection());
            }
        }, function ($query) {
            // 默认排序规则
            $query->orderBy('number', 'desc');
        })
        ->when(
            $this->columnSearch['client'] ?? null,
            fn ($query, $title) => $query->where('clients.title', 'like', '%' . $title . '%')
        )
        ->when(
            $this->columnSearch['title'] ?? null,
            fn ($query, $title) => $query->where('jobs.title', 'like', '%' . $title . '%')
        );
}

3. 性能优化方案：用JOIN替代子查询 #

子查询在数据量大时性能较差，推荐使用LEFT JOIN + GROUP BY的方式统计工作项数量，排序更直接高效：

public function builder(): Builder
{
    return Job::query()
        ->select([
            'jobs.*',
            DB::raw('COUNT(job_work_items.id) as wi_count')
        ])
        ->leftJoin('job_work_items', 'job_work_items.job_number', '=', 'jobs.number')
        ->where('jobs.is_draft', 0)
        ->where('jobs.redirect', 0)
        ->with('client')
        ->groupBy('jobs.id') // 若触发ONLY_FULL_GROUP_BY错误，可将jobs表所有字段加入GROUP BY，或临时关闭mysql的strict模式
        ->when($this->getSortColumn(), function ($query, $column) {
            if ($column === 'wi_count') {
                $query->orderBy('wi_count', $this->getSortDirection());
            } else {
                $query->orderBy($column, $this->getSortDirection());
            }
        }, function ($query) {
            $query->orderBy('jobs.number', 'desc');
        })
        ->when(
            $this->columnSearch['client'] ?? null,
            fn ($query, $title) => $query->where('clients.title', 'like', '%' . $title . '%')
        )
        ->when(
            $this->columnSearch['title'] ?? null,
            fn ($query, $title) => $query->where('jobs.title', 'like', '%' . $title . '%')
        );
}

注意事项 #

• 若使用GROUP BY出现ONLY_FULL_GROUP_BY错误，优先选择在GROUP BY中列出jobs表的所有字段，而非关闭strict模式；
• 确保job_work_items表的job_number字段已添加索引，提升关联查询性能。

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