Error related to only_full_group_by when executing a query in HeidiSQL

Question

I want to create a view in my DB, and below are my code and Error message

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ucarer_server.ord_order.request_patient_id" which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

and also, I've checked the answer on stackoverflow, too Here's the link

Error related to only_full_group_by when executing a query in MySql

But, still can't work for me... Can anyone tell me where can I fix?

select
   ord_order.`request_patient_id`,
   ord_order.`order_id`,
   ord_order.`bundle_id`,
   gvr_caregiver.`surname` as gvr_surname,
   ord_order.`gvr_response_datetime` as 'gvr_response_datetime',
   usr_user.user_id,
   ord_order.`order_status_code_abbr`,
   gvr_caregiver.`first_name` as gvr_first_name,
   IFNULL(ord_order.`bundle_number`, ord_order.`order_number`) as bundleOrOrderNumber,
   ord_order.order_status_last_update_datetime
from ord_order
left join gvr_caregiver on ord_order.srv_caregiver_id = gvr_caregiver.caregiver_id
left join usr_user on usr_user.patient_id = ord_order.request_patient_id
group by bundleOrOrderNumber;

Answer 1

That is happening because ord_order.request_patient_id is not a part of group by or part of a where condition. When you are doing a group by on bundleOrOrderNumber, the patient id can assume many values. You need to do either a group by or a where condition on all the selected expressions.

Note that this only happens when a specific sql mode ONLY_FULL_GROUP_BY. You can do a select @@sql_mode to see what all sql modes are present. If you remove this sql mode, your query will work fine but I don't recommend that as there is a chance of the query being wrong.

Answer 2

I guess this is related with strict mode so disable group by check by modifying the connection config. Edit your config/database.php and add the following to the selected connection array:

'strict'    => true,
            'modes'     => [
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_AUTO_CREATE_USER',
                'NO_ENGINE_SUBSTITUTION'
            ],

It will keep strict mode on but with group by checks disabled