lundi 21 mars 2022

groupby Not working with laravel and PostreSQL

I am using PostreSQL . when I try to use groupBy in my laravel query. It showing Grouping error:

$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();

Then I tried with DISTINCT ON It showing syntax error

$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();


via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire