PDOException:SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated [duplicate]

Question

My Yii framework website was working fine until I decided to upgrade the PHP. It was 5+ version.

My current environment:

1. ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))
2. mysql  Ver 8.0.33-0
3. PHP version: 8.1.21 
4. PHPMyAdmin: 5.1.1deb5ubuntu1 

After I updated the PHP I am getting these errors

1/

[error][yii\db\Exception] PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'connect.userDeviceLink.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /var/www/html/connect/vendor/yiisoft/yii2/db/Command.php:1302
Stack trace:
#0 /var/www/html/connect/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute()
#5 /var/www/html/connect/models/UserDeviceLink.php(393): yii\db\ActiveQuery->all()
#6 /var/www/html/connect/models/UserDeviceLink.php(557): app\models\UserDeviceLink->findAllDevicesByUser()
#7 /var/www/html/connect/controllers/PlatformController.php(767): app\models\UserDeviceLink->getDeviceDataByUser()
#15 {main}

2/

Next yii\db\Exception: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'connect.userDeviceLink.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The SQL being executed was: SELECT `userDeviceLink`.* FROM `userDeviceLink` LEFT JOIN `deviceSyscodata` ON `userDeviceLink`.`deviceSerial` = `deviceSyscodata`.`serial` LEFT JOIN `deviceNames` ON `deviceSyscodata`.`type` = `deviceNames`.`type` WHERE `userId` IN (8935, 39, 42, 47, 48, 8906, 8911, 8912, 8913, 8914, 8915, 8916, 8917, 8918, 8921, 8922, 8923, 8924, 8926, 8927, 8928, 8929, 8930, 8931, 8932, 8933, 8934, 8935, 8940, 8941, 8946, 8948, 8950, 8951, 8952, 8953, 8954, 8956, 8958, 8959, 8960, 8961, 8962, 8963, 8964, 8965,...) GROUP BY `deviceSerial` ORDER BY `timestamp` DESC in /var/www/html/connect/vendor/yiisoft/yii2/db/Schema.php:678
Stack trace:
#0 /var/www/html/connect/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException()
#5 /var/www/html/connect/models/UserDeviceLink.php(393): yii\db\ActiveQuery->all()
#6 /var/www/html/connect/models/UserDeviceLink.php(557): app\models\UserDeviceLink->findAllDevicesByUser()
#7 /var/www/html/connect/controllers/PlatformController.php(767): app\models\UserDeviceLink->getDeviceDataByUser()

#15 {main}
Additional Information:
Array
(
    [0] => 42000
    [1] => 1055
    [2] => Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'connect.userDeviceLink.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
)

I understand these two errors occurs because:

If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default from version 5.7.5), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to non-aggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.

I needed a quick fix because the site is really big. if I fix this soemwhere another file will have the same issue. So I added this line to my /etc/mysql/my.cnf

[mysqld]
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

This is my full my.cnf

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

This is my sql folder (that confuses me a little) screenshot I am not sure .cnf file where I added is correct file or not. After I added that , and restarted the mysql, the page load took a longer time but still the page is blank.

This method can give incorrect queries sometimes also it makes the site slow. So I tried to fix the query code.

as per the stacktrace I have to update these files and lines to fix the first error:

#5 /var/www/html/connect/models/UserDeviceLink.php(393): yii\db\ActiveQuery->all()
#6 /var/www/html/connect/models/UserDeviceLink.php(557): app\models\UserDeviceLink->findAllDevicesByUser()
#7 /var/www/html/connect/controllers/PlatformController.php(767): app\models\UserDeviceLink->getDeviceDataByUser()

Line 393 is ->all())) { from this method:

public function findAllDevicesByUser($userId, $order = 'claimedAt')
    {

        $userId = !($userId) ? Yii::$app->user->id : $userId;
        $userIds = [];
        array_push($userIds, $userId);
        $myIds = User::instance()->getMyUsersIds($userId);
        #return $myIds; 
        

        if ($myIds) {
            foreach ($myIds as $key => $value) {
                array_push($userIds, $value);
            }
            array_unique($userIds);
        }


        if (!empty($devices = self::find()
            ->joinWith('device')
            ->orderBy($order . ' DESC')
            ->where(["userId" => $userIds])
            ->groupBy('deviceSerial')
            ->asArray()
            ->all())) {             
            return $devices;
        }
        return null;
    }

So I updated it to

 public function findAllDevicesByUser($userId, $order = 'claimedAt')
    {
        $userId = !($userId) ? Yii::$app->user->id : $userId;
        $userIds = [];
        array_push($userIds, $userId);
        $myIds = User::instance()->getMyUsersIds($userId);
        
        if ($myIds) {
            foreach ($myIds as $key => $value) {
                array_push($userIds, $value);
            }
            array_unique($userIds);
        }

        $devices = self::find()
            ->select(['deviceSerial', 'MAX(id) AS id']) // Add the non-aggregated column 'id' to the SELECT list
            ->joinWith('device')
            ->orderBy($order . ' DESC')
            ->where(["userId" => $userIds])
            ->groupBy('deviceSerial') // Group by 'deviceSerial' instead of 'id'
            ->asArray()
            ->all();

        if (!empty($devices)) {             
            return $devices;
        }
        
        return null;
    }

I think my updated code is not the correct SELECT way to do this and it does not work.

Questions:

1. Is it ok now to update the .cnf file? if ok, why it is not working me after making the change?
2. How do I have to update the above methods to comply with the syntax?