Yii2 groupBy. How to solve the problem?

Question

Yii

Database Exception - yii\db\Exception
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'rentaltrans.item.alias' 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 COUNT(*) FROM (SELECT `item`.* FROM `item` LEFT JOIN `user` ON `item`.`user_id` = `user`.`id ` LEFT JOIN `order` ON `item`.`id` = `order`.`item_id` WHERE ((`user`.`status`=10) AND (`item`.`status`='available') AND (`item`.`deleted`=0)) AND (`user`.`hide_items`=0) AND (`category_id`=1) GROUP BY `item`.`id` ORDER BY `order`.`id` DESC) `c`
Help solve the problem.
Here is my code.

$item = Item::find()
            ->joinWith(['user', 'order'])
          ->where(['user.status' => User::STATUS_ACTIVE,'item.status' => 'available', 'item.deleted' => 0])
          ->andWhere(['user.hide_items' => 0])
          ->groupBy('item.id');

Answer 1

Try:

$item = Item::find()->select(['item.*'])
            ->joinWith(['user', 'order'])
          ->where(['user.status' => User::STATUS_ACTIVE,'item.status' => 'available', 'item.deleted' => 0])
          ->andWhere(['user.hide_items' => 0])
          ->groupBy('item.id');

Answer 2

Your request is incompatible with the only_full_group_by mode, there are several possible solutions to the problem:

• Disable mode sql_mode = only_full_group_byin MySQL settings (my.cnf)
  
• Set the required mode when connecting (if you are using PDO), example:
  

  $pdo = new PDO(
       $dsn, 
       $username, 
       $password, 
       array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode="TRADITIONAL"') 
  );

  
  I don't work with Yii, but the config file should look something like this:
  

  [
    'type' => 'PDO',
    'connection' => [
      'dsn'  => 'mysql:host=localhost;dbname=database',
      'username'   => 'user',
      'password'   => 'password',
      'persistent' => FALSE,
      'options'  => [
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode="TRADITIONAL"'
        ],
    ],
    'charset'    => 'utf8',
    'caching'    => FALSE,
  ]

  
  

• Set required mode for session:
  Add only one mode to sql_mode without deleting existing ones:
  Remove only one mode from sql_mode without removing others:
  If you want to remove only the ONLY_FULL_GROUP_BY mode, use the command below: