如何在Laravel DataTable中搜索非表计算字段orders_count

问题场景 #

在Laravel中使用DataTable时，存在一个未存储在数据库表中的计算字段orders_count，该字段通过关联orders表统计订单数生成。其他字段可通过现有代码正常搜索，但对orders_count使用相同方法时，出现「字段不存在」的错误，需实现该计算字段的搜索功能。

相关代码 #

$data = Visit::leftJoin('users', 'users.id', '=', 'visits.user_id')
    ->leftJoin('customers', 'visits.customer_id', '=', 'customers.id')
    ->leftJoin('orders', 'visits.id', '=', 'orders.visit_id')
    ->select(
        'visits.id as visit_id',
        DB::raw('DATE_FORMAT(visits.created_at, "%d-%b-%Y") as visit_date'),
        'users.email as email',
        'customers.name as customer_name',
        DB::raw('count(orders.id) as orders_count')
    )
    ->groupBy('visits.id')
    ->skip($start)
    ->take($rowperpage);

$totalRecords = Visit::select('count(*) as allcount')->count();
$totalRecordswithFilter = Visit::leftJoin('users', 'users.id', '=', 'visits.user_id')
    ->leftJoin('customers', 'visits.customer_id', '=', 'customers.id')
    ->leftJoin('orders', 'visits.id', '=', 'orders.visit_id')
    ->select('count(*) as allcount');

foreach ($columnName_arr as $col) {
    if ($col['search']['value'] != '') {
        $searchValue = $col['search']['value'];
        $searchColumn= $col['data'];
    }

    if ($searchColumn != null) {
        if ($searchColumn == 'visit_id') {
            $totalRecordswithFilter->where(function($query) use ($searchValue) {
                $query->where('visits.id', 'like', '%' .$searchValue . '%');
            });

            $data->where(function($query) use ($searchValue) {
                $query->where('visits.id', 'like', '%' .$searchValue . '%');
            });
        }

        if ($searchColumn == 'visit_date') {
            $totalRecordswithFilter->where(function($query) use ($searchValue) {
                $query->where('visits.created_at', 'like', '%' .$searchValue . '%');
            });

            $data->where(function($query) use ($searchValue) {
                $query->where('visits.created_at', 'like', '%' .$searchValue . '%');
            });
        }

        if ($searchColumn=='email') {
            $totalRecordswithFilter->where(function($query) use ($searchValue) {
                $query->where('users.email', 'like', '%' .$searchValue . '%');
            });

            $data->where(function($query) use ($searchValue) {
                $query->where('users.email', 'like', '%' .$searchValue . '%');
            });
        }

        if ($searchColumn == 'customer_name') {
            $totalRecordswithFilter->where(function($query) use ($searchValue) {
                $query->where('customers.name', 'like', '%' .$searchValue . '%');
            });

            $data->where(function($query) use ($searchValue) {
                $query->where('customers.name', 'like', '%' .$searchValue . '%');
            });
        }

        // 此处为问题代码
        if ($searchColumn == 'orders_count') {
            $totalRecordswithFilter->where(function($query) use ($searchValue) {
                $query->where('orders_count', 'like', '%' . $searchValue . '%');
            });

            $data->where(function($query) use ($searchValue) {
                $query->where('orders_count', 'like', '%' . $searchValue . '%');
            });
        }

        if ($searchColumn=='status') {
            $totalRecordswithFilter->where(function($query) use ($searchValue) {
                $query->where('orders.status', 'like', '%' .$searchValue . '%');
            });

            $data->where(function($query) use ($searchValue) {
                $query->where('orders.status', 'like', '%' .$searchValue . '%');
            });
        }
    } else {
        $totalRecordswithFilter->where(function($query) use ($searchValue) {
            return $query->where('visits.id', 'like', '%' .$searchValue . '%')
                ->orWhere('visits.created_at', 'like', '%' .$searchValue . '%')
                ->orWhere('users.email', 'like', '%' .$searchValue . '%')
                ->orWhere('customers.name', 'like', '%' .$searchValue . '%');
        });
    }
}

问题原因 #

orders_count是通过COUNT(orders.id)计算得到的查询别名，SQL执行顺序中WHERE子句在SELECT和GROUP BY之前执行，此时orders_count还未生成，因此直接用WHERE 'orders_count' LIKE ...会触发「字段不存在」错误。

解决方案 #

方案一：使用HAVING子句过滤分组结果 #

由于查询已经通过GROUP BY visits.id分组，HAVING子句会在分组后执行，可以直接引用聚合函数或其别名。修改orders_count对应的搜索逻辑：

if ($searchColumn == 'orders_count') {
    // 处理数据查询：用HAVING过滤分组后的计算字段
    $data->having(function($query) use ($searchValue) {
        // 方式1：直接使用别名（MySQL支持）
        $query->having('orders_count', 'like', "%{$searchValue}%");
        // 方式2：重复聚合函数写法（兼容更多数据库）
        // $query->havingRaw('count(orders.id) like ?', ["%{$searchValue}%"]);
    });

    // 处理带筛选的总记录数查询：先分组再统计符合条件的分组数
    $totalRecordswithFilter->groupBy('visits.id')
        ->having('orders_count', 'like', "%{$searchValue}%")
        ->selectRaw('count(*) as allcount');
}

方案二：通过子查询预先计算orders_count #

将包含orders_count的查询作为子查询，外层查询直接将其视为普通字段进行搜索，逻辑更直观：

// 1. 预先构建包含orders_count的子查询
$visitsWithOrders = Visit::leftJoin('users', 'users.id', '=', 'visits.user_id')
    ->leftJoin('customers', 'visits.customer_id', '=', 'customers.id')
    ->leftJoin('orders', 'visits.id', '=', 'orders.visit_id')
    ->select(
        'visits.id as visit_id',
        DB::raw('DATE_FORMAT(visits.created_at, "%d-%b-%Y") as visit_date'),
        'users.email as email',
        'customers.name as customer_name',
        DB::raw('count(orders.id) as orders_count')
    )
    ->groupBy('visits.id');

// 2. 基于子查询构建数据列表查询
$data = DB::table($visitsWithOrders, 'visits_stats')
    ->skip($start)
    ->take($rowperpage);

// 3. 基于子查询构建带筛选的总记录数查询
$totalRecordswithFilter = DB::table($visitsWithOrders, 'visits_stats')
    ->select('count(*) as allcount');

// 4. 修改orders_count的搜索逻辑，直接用WHERE即可
if ($searchColumn == 'orders_count') {
    $totalRecordswithFilter->where('orders_count', 'like', "%{$searchValue}%");
    $data->where('orders_count', 'like', "%{$searchValue}%");
}

全局搜索补充（可选） #

如果需要在全局搜索中包含orders_count，修改else分支的查询逻辑：

$totalRecordswithFilter->where(function($query) use ($searchValue) {
    return $query->where('visits.id', 'like', '%' .$searchValue . '%')
        ->orWhere('visits.created_at', 'like', '%' .$searchValue . '%')
        ->orWhere('users.email', 'like', '%' .$searchValue . '%')
        ->orWhere('customers.name', 'like', '%' .$searchValue . '%')
        // 方案一：添加HAVING条件
        ->orHaving('orders_count', 'like', "%{$searchValue}%")
        // 方案二：如果用子查询，直接添加orWhere
        // ->orWhere('orders_count', 'like', "%{$searchValue}%")
});

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