0

I get an error when I try to add new table field

(PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 
Expression #17 of 
SELECT list is not in GROUP BY clause and contains nonaggregated column 'c.color_slug' 
which is not functionally dependent on columns in GROUP BY clause; this 
is incompatible with sql_mode=only_full_group_by

This works:

$products = DB::table('product_color_relations')
                ->select( 'p.product_slug' , 'p.product_id' , 'p.product_slug' , 'p.product_name' , 'p.product_ref' , 'p.product_img' , 'p.product_price' , 'p.product_sale_price' , 'p.product_stock' , 'p.product_unit' , 'p.created_at')
                ->join('colors as c' , 'c.color_id' , '=' , 'product_color_relations.r_color_id')
                ->join('products as p' , 'p.product_id' , '=' , 'product_color_relations.r_product_id')
                ->groupBy('product_color_relations.r_product_id')
                ->havingRaw('MAX(`product_color_relations`.`r_color_id`) = MIN(`product_color_relations`.`r_color_id`) AND MIN(c.color_slug) = ? ' , [ $var['v'] ] )
                ->get();

this doesn't:

$products = DB::table('product_color_relations')
                ->select( 'p.product_slug' , 'p.product_id' , 'p.product_slug' , 'p.product_name' , 'p.product_ref' , 'p.product_img' , 'p.product_price' , 'p.product_sale_price' , 'p.product_stock' , 'p.product_unit' , 'p.created_at' , 'c.color_slug')
                ->join('colors as c' , 'c.color_id' , '=' , 'product_color_relations.r_color_id')
                ->join('products as p' , 'p.product_id' , '=' , 'product_color_relations.r_product_id')
                ->groupBy('product_color_relations.r_product_id')
                ->havingRaw('MAX(`product_color_relations`.`r_color_id`) = MIN(`product_color_relations`.`r_color_id`) AND MIN(c.color_slug) = ? ' , [ $var['v'] ] )
                ->get();

Difference between them that I added to the select method this field: 'c.color_slug' I can't understand why I'm getting this error. In plain php it works.

$sql = "select `p`.`product_slug`, `p`.`product_id`, `p`.`product_slug`, `p`.`product_name`, `p`.`product_ref`, `p`.`product_img`, `p`.`product_price`, `p`.`product_sale_price`, `p`.`product_stock`, `p`.`product_unit`, `p`.`created_at` , c.color_slug from `product_color_relations` inner join `colors` as `c` on `c`.`color_id` = `product_color_relations`.`r_color_id` inner join `products` as `p` on `p`.`product_id` = `product_color_relations`.`r_product_id` group by `product_color_relations`.`r_product_id` having MAX(`product_color_relations`.`r_color_id`) = MIN(`product_color_relations`.`r_color_id`) AND MIN(c.color_slug) = 'white'"
Nurqm
  • 4,715
  • 2
  • 11
  • 35
rocket_moon
  • 309
  • 4
  • 18
  • Does this answer your question? [SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql\_mode=only\_full\_group\_by](https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc) – Dark Knight Oct 06 '20 at 17:22
  • The group by `product_color_relations.r_product_id` have a lot of rows with different `c.color_slug)` - what one of them do you want to retrieve? Server cannot understand this and throws error. – Akina Oct 06 '20 at 17:33
  • @Akina no, it only has one color. It is the same color to all the results. I just need some information of that column (translation). If i do this sql query in plain php, it works, but in laravel throws that error. – rocket_moon Oct 06 '20 at 17:47
  • @JitendraYadav , not, the query works in phpmyadmin directly and in plain php. (Same server) – rocket_moon Oct 06 '20 at 17:48
  • *no, it only has one color* If so then add `c.color_slug` into `groupBy` expression and remove wrapping `MIN()` in `havingRaw`. – Akina Oct 06 '20 at 18:00
  • @Akina , this ``$sql = "select `... MIN(c.color_slug) = 'white'"`` works in raw sql and plain PHP , when converted to laravel it doesn't. All the ``c.color_slug`` are white. Ran the query i got 304 results all of them are ``["color_slug"]=>string(5) "white" `` – rocket_moon Oct 07 '20 at 08:03

0 Answers0