How to find product by manufacturer?-CodePudding

I am using Laravel 8 and using Raw Query. I have added query to fetch count of product by manufacturers but facing below error.

SQLSTATE[42000]: Syntax error or access violation: 1055 'ngtonlin_superadmin.m.manufacturerid'
isn't in GROUP BY (SQL: select `m`.`manufacturerid` as `id`, `m`.`name`, `m`.`logowidth`,
`m`.`logoheight`, count(p.id) as total from `products` as `p` inner join `manufacturer` as `m` on
`m`.`manufacturerid` = `p`.`manufacturer_id` group by `p`.`manufacturer_id` order by `total` desc limit 5)

and my query is

DB::table('products as p')
        ->select('m.manufacturerid as id','m.name','m.logowidth','m.logoheight', DB::raw('count(p.id) as total'))
        ->join('manufacturer as m','m.manufacturerid','=','p.manufacturer_id')
        ->groupBy('p.manufacturer_id')
        ->orderBy('total', 'DESC')
        ->limit(5)
        ->get();

CodePudding user response：

I think you need to use

->groupBy(m.manufacturerid, 'm.name','m.logowidth','m.logoheight')

Not

->groupBy('p.manufacturer_id')

Because, I think p.manufacturer_id = m.manufacturerid, and you use m.manufacturerid in the select part.

CodePudding user response：

Try this: DB::table('products as p')->join('manufacturer as m','m.manufacturerid','p.manufacturer_id')->selectRaw('m.manufacturerid as id')->selectRaw('m.name')->selectRaw('m.logowidth')->selectRaw('m.logoheight')->selectRaw('count(p.id) as total')->groupBy('p.manufacturer_id')->orderBy('total', 'DESC')->limit(5)->get();