Laravel 5.6中Yajra DataTable实现关联表字段求和与搜索

我来帮你完善这段代码，搞定关联表starting_balance的求和以及该字段的搜索功能，下面是分步实现方案：

1. 补全并优化查询逻辑 #

首先要完善关联表的连接、分组操作（因为用到了聚合函数SUM），同时处理空值避免前端显示异常：

use Auth;
use DB;
use App\Models\Inventory;
use Yajra\DataTables\Facades\DataTables;

public function getInventoryData()
{
    $userId = Auth::user()->id;

    // 构建基础查询：关联关联表、求和、过滤当前用户数据
    $query = Inventory::with(['contact', 'warehouse'])
        ->select(
            'inventories.*',
            // 用COALESCE把空求和结果转为0，避免显示NULL
            DB::raw('COALESCE(SUM(inventory_has_warehouses.starting_balance), 0) as total')
        )
        ->leftJoin('inventory_has_warehouses', 'inventories.id', '=', 'inventory_has_warehouses.inventory_id')
        ->where('inventories.user_id', $userId) // 过滤当前用户的库存数据
        ->groupBy('inventories.id'); // 必须按库存主键分组，否则聚合函数会出问题

2. 配置Yajra DataTable 支持聚合字段搜索 #

聚合字段的搜索不能用默认的where，需要用havingRaw来处理分组后的结果过滤，同时配置前端显示的列：

return DataTables::of($query)
        // 自定义过滤逻辑，处理total字段的搜索
        ->filter(function ($query) {
            $searchValue = request()->input('search.value');
            if (!empty($searchValue)) {
                // 用havingRaw匹配聚合后的total值
                $query->havingRaw('COALESCE(SUM(inventory_has_warehouses.starting_balance), 0) LIKE ?', ["%{$searchValue}%"]);
            }
        })
        // 自定义显示关联的联系人名称
        ->addColumn('contact_name', function ($inventory) {
            return $inventory->contact ? $inventory->contact->name : '-';
        })
        // 自定义显示关联的仓库名称（如果是多对多关联，可改为遍历输出多个仓库）
        ->addColumn('warehouse_name', function ($inventory) {
            return $inventory->warehouse ? $inventory->warehouse->name : '-';
        })
        // 格式化显示求和后的余额
        ->addColumn('total_balance', function ($inventory) {
            return number_format($inventory->total, 2);
        })
        ->rawColumns(['contact_name', 'warehouse_name', 'total_balance'])
        ->make(true);
}

3. 前端DataTable配置（可选） #

如果需要明确指定前端列的搜索属性，可以在JS初始化时标记total_balance为可搜索：

$(document).ready(function() {
    $('#inventory-table').DataTable({
        processing: true,
        serverSide: true,
        ajax: '{{ route('inventory.data') }}', // 替换成你的路由地址
        columns: [
            { data: 'id', name: 'inventories.id' },
            { data: 'name', name: 'inventories.name' },
            { data: 'contact_name', name: 'contact.name' },
            { data: 'warehouse_name', name: 'warehouse.name' },
            { data: 'total_balance', name: 'total', searchable: true } // 标记该字段支持搜索
        ]
    });
});

关键注意事项 #

• 分组必须存在：使用SUM这类聚合函数时，必须按主表inventories的主键分组，否则会出现SQL语法错误或重复数据。
• 空值处理：用COALESCE把无关联数据时的求和结果转为0，避免前端显示null。
• 关联关系验证：确保你的Inventory模型已经正确定义了关联：

  // Inventory模型示例
  public function contact()
  {
      return $this->belongsTo(Contact::class);
  }
  
  public function warehouse()
  {
      // 如果是多对多关联，用belongsToMany
      return $this->belongsToMany(Warehouse::class, 'inventory_has_warehouses');
  }
  

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