0

I have Clients and ClientsLimitCategory table, i want joining these table using leftJoin in Laravel and counting when i am grouping thats value.

I have working MySQL query like this:

SELECT A.* ,  COUNT(*)  AS TotalClient FROM `ClientsLimitCategory` A
LEFT JOIN `Clients` B ON B.AgreementType = A.CategoryName
WHERE A.CategoryName ='GOLD'
GROUP BY A.CategoryName

And using this code in Laravel :

$keyword = (!empty(isset($arrSearch['keyword'])) && $arrSearch['keyword'] != "undefined") ? $arrSearch['keyword'] : '';
$active = (!empty(isset($arrSearch['active'])) && $arrSearch['active'] != "undefined") ? $arrSearch['active'] : '';
// $type = (!empty(isset($arrSearch['type'])) && $arrSearch['type'] != "undefined") ? $arrSearch['type'] : '';

$sql = DB::table($this->table)
        ->leftJoin('Clients', 'AgreementType','=','ClientsLimitCategory.CategoryName')
        ->select('ClientsLimitCategory.*',DB::raw('count(*) as TotalClient'))
        ->where(function($query) use ($keyword){
            $query->where('CategoryName', 'like', '%' .$keyword. '%')
                  ->orWhere('CategoryDescription','like','%'.$keyword.'%');
        });

$sql =  (!empty(isset($active)) && $active != "") ? $sql->where('active','=',$active) : $sql;
$sql =  (!empty(isset($type)) && $type != "") ? $sql->where('type','=',$type) : $sql;
$sql =  $sql->groupBy('ClientsLimitCategory.CategoryName');
//             // ->get();

$sql = $sql->orderBy('ClientsLimitCategory.CreatedDate','DESC');
$hasil['count'] = $sql->count();
$sql = $sql->paginate($limit);
$hasil['data'] = $sql;
$hasil['prinQuery'] = DB::getQueryLog();

But i got this error:

Error Message

Douwe de Haan
  • 6,247
  • 1
  • 30
  • 45
Dede kurniawan
  • 51
  • 5
  • I would say that `ONLY_FULL_GROUP_BY` is enabled in your database which used by Laravel – Nergal Jul 03 '18 at 08:39
  • this may help you https://stackoverflow.com/questions/43776758/how-can-i-solve-incompatible-with-sql-mode-only-full-group-by-in-laravel-eloquen – rkj Jul 03 '18 at 08:51
  • @rkj Thanks, my eloquent work after disableing strict, but there's have side effect for my app security? – Dede kurniawan Jul 03 '18 at 09:55

0 Answers0