Laravel error when get data from mySQL using groupBy

Question

I want to get Data from mySQL where its only get 1 id_product,so when there is 2 data with same id_product value.. its only get the first one.I have tried the query in mySQL and its work, the query is like this

select `product_photo`.*, `product`.`id_merchant` from `product_photo` inner join `product` on 
`product_photo`.`id_product` = `product`.`id` where `product`.`id_merchant` = 11 group by 
`product_photo`.`id_product`

but i got error when i implemented it to my laravel controller,the code is like this

$id_merchant = $request->input('id_merchant');

    $photo=DB::table('product_photo')
    ->join('product','product_photo.id_product','=','product.id')
    ->select('product_photo.*','product.id_merchant')
    ->where('product.id_merchant','=',$id_merchant)
    ->groupBy('product_photo.id_product')
    ->get();
    
    return response()->json(['success'=>true,'message'=>'success', 'data' => $photo],200);

Can someone tell me why my laravel got error ? Sorry my english

try to print query from laravel (https://stackoverflow.com/questions/18236294/how-do-i-get-the-query-builder-to-output-its-raw-sql-query-as-a-string) and see what's the difference. — shyammakwana.me, Feb 06 '21 at 08:01
@shyammakwana.me There is no different query between mySql query and in laravel query — justinus andreas, Feb 06 '21 at 08:19
@Silidrone Syntax error or access violation: 1055 'merkha.product_photo.id' isn't in GROUP BY (SQL: select `product_photo`.*, `product`.`id_merchant` from `product_photo` inner join `product` on `product_photo`.`id_product` = `product`.`id` where `product`.`id_merchant` = 11 group by `product_photo`.`id_product`) this is what i got from postman — justinus andreas, Feb 06 '21 at 08:19

score 2 · Accepted Answer · answered Feb 06 '21 at 08:33

2

This is caused by MySQL's strict mode. Change strict to false in your config/database.php file.

When you open the file, in the mysql array, set strict => false. After you disable MySQL's strict mode it shouldn't show the error anymore.

answered Feb 06 '21 at 08:33

Silidrone

1,471
4
20
35

@justinusandreas this is not a good practice to disable strict mode, your posted query is invalid because your selecting columns that are not present in group by clause or aggregated see [here](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) – M Khalid Junaid Feb 06 '21 at 13:56

Laravel error when get data from mySQL using groupBy

1 Answers1