-1

I have the following query which is working fine if executed directly from a phpmyadmin interface

SELECT cat.category_id, catName.`name` AS catName, cat.* 
FROM corehub_online_product p 
LEFT JOIN corehub_online_product_to_category cat 
ON cat.product_id = p.product_id 
LEFT JOIN corehub_online_category grp 
ON cat.category_id = grp.category_id 
LEFT JOIN corehub_online_category_description catName 
ON catName.category_id = grp.category_id 
LEFT JOIN corehub_online_category_description grpName 
ON grpName.category_id = grp.parent_id 
WHERE grp.parent_id = 49 AND cat.category_id != '42' 
GROUP BY catName

The above sql is generated inside the php code (Laravel framework 8.x) and the statements for the same are given below

$dbPrefix = "corehub";
        
         $categoryQP = "
    SELECT cat.category_id, catName.`name` AS catName, cat.*
    FROM ".$dbPrefix."_online_product p
    LEFT JOIN ".$dbPrefix."_online_product_to_category cat ON cat.product_id = p.product_id
    LEFT JOIN ".$dbPrefix."_online_category grp ON cat.category_id = grp.category_id
    LEFT JOIN ".$dbPrefix."_online_category_description catName ON catName.category_id = grp.category_id
    LEFT JOIN ".$dbPrefix."_online_category_description grpName ON grpName.category_id = grp.parent_id
    WHERE grp.parent_id = 49 AND cat.category_id != '42'
    GROUP BY catName";
        
        
        $categories = DB::select($categoryQP);

When the above code is executed it throws the following error.

Illuminate\Database\QueryException SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yogamaui_testportal.cat.category_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: SELECT cat.category_id, catName.name AS catName, cat.* FROM corehub_online_product p LEFT JOIN corehub_online_product_to_category cat ON cat.product_id = p.product_id LEFT JOIN corehub_online_category grp ON cat.category_id = grp.category_id LEFT JOIN corehub_online_category_description catName ON catName.category_id = grp.category_id LEFT JOIN corehub_online_category_description grpName ON grpName.category_id = grp.parent_id WHERE grp.parent_id = 49 AND cat.category_id != '42' GROUP BY catName)

Can anyone please advice on what might be the problem?

user3783243
  • 5,368
  • 5
  • 22
  • 41
logeeks
  • 4,849
  • 15
  • 62
  • 93

1 Answers1

0

That's not really Laravel preferred way,

But disabling the strict mode in database.php should make it work

// config/database.php

    'connections' => [
        'mysql' => [
            ...
            'strict' => false, // <---
            ...
        ],
    ],
Mikael Dalholm
  • 121
  • 1
  • 8
  • doesn't work with me, commenting the mode make it work in my case (laravel 9x) ` 'modes' => [ 'STRICT_TRANS_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'ERROR_FOR_DIVISION_BY_ZERO', // 'NO_AUTO_CREATE_USER', 'NO_ENGINE_SUBSTITUTION' ] ` – Remo Harsono Jul 15 '23 at 15:14