groupby 不支持 laravel 和 PostreSQL

Question

我正在使用 PostreSQL 。当我尝试在 Laravel 查询中使用 groupBy 时。它显示分组错误：

$available_Area = DB::table('area AS a')
->select('a.*','a.name as a_name', 'f.name as f_name', 'zc.code as zc_code') 
->join('transaction AS t', 't.f_id', '=', 'a.f_id')
->leftjoin('facility AS f', 'f.id', '=', 't.f_id')
->leftJoin('transaction_detail AS td', 'td.t_id', '=', 't.id')
->leftJoin('zone_component AS zc', 'zc.id', '=', 'td.comp_id') 
->whereNotIn('a.id', DB::table('transaction_detail AS td')
            ->select('zc.a_id')
            ->join('zone_component AS zc', 'zc.id', '=', 'td.comp_id')                  
            ->whereIn('td.card_type', ['C_OVA','C_M21'])
                            ->where('td.t_id', $url)
                        )
            ->groupBy('a.id')
->where('t.id', @$url) 
->get();

然后我尝试使用DISTINCT ON它显示语法错误

$available_Area = DB::table('area AS a')
->select('a.*','a.name as a_name', 'f.name as f_name', 'zc.code as zc_code', DB::raw("DISTINCT ON(a.id)")) 
->join('transaction AS t', 't.f_id', '=', 'a.f_id')
->leftjoin('facility AS f', 'f.id', '=', 't.f_id')
->leftJoin('transaction_detail AS td', 'td.t_id', '=', 't.id')
->leftJoin('zone_component AS zc', 'zc.id', '=', 'td.comp_id') 
->whereNotIn('a.id', DB::table('transaction_detail AS td')
            ->select('zc.a_id')
            ->join('zone_component AS zc', 'zc.id', '=', 'td.comp_id')                  
            ->whereIn('td.card_type', ['C_OVA','C_M21'])
                            ->where('td.t_id', $url)
                        )
->where('t.id', @$url) 
->get();

Answer 1

您不能使用select a.*，因此您应该将所有字段添加到group by语句或应用聚合函数。例如，将字段 a.id、a.name、f.name 添加到分组依据，并在字段 zc 上应用 min 函数。代码：

$available_Area = DB::table('area AS a')
    ->select('a.id','a.name as a_name', 'f.name as f_name', 'min(zc.code) as zc_code') 
    ->join('transaction AS t', 't.f_id', '=', 'a.f_id')
    ->leftjoin('facility AS f', 'f.id', '=', 't.f_id')
    ->leftJoin('transaction_detail AS td', 'td.t_id', '=', 't.id')
    ->leftJoin('zone_component AS zc', 'zc.id', '=', 'td.comp_id') 
    ->whereNotIn('a.id', 
        DB::table('transaction_detail AS td')
            ->select('zc.a_id')
            ->join('zone_component AS zc', 'zc.id', '=', 'td.comp_id')                  
            ->whereIn('td.card_type', ['C_OVA','C_M21'])
            ->where('td.t_id', $url)
    )
    ->groupBy('a.id', 'a.name', 'f.name')
    ->where('t.id', @$url) 
    ->get();

一些简化的示例