PHP YII2 set session group_concat_max_len

Question

$model = Item::find()
->select('group_concat(item.ID) AS ItemID')
->joinWith('image', true, 'LEFT JOIN')
->where('item.user_id = :id', [':id' => Yii::$app->user->identity->ID])
->all();

You see, if I do like this it will print me the string of itemID with 145 elements separated by comma, but if there are 3000k records in db, so it will output me only half of them.

The question is, is it possible to set session group_concat_max_len in Yii2 framework?

Answer 1

You can access your application's database connection object using Yii::$app->db. You can then set your mysql variable just before the call to find:

Yii::$app->db->createCommand('SET group_concat_max_len = $length')->execute();
$model = Item::find()
...

For consistency, you can set your group_concat_max_len as a Yii parameter in your config file to make it accessible application-wide.

'params' => [
    ...
    'group_concat_max_len' => 50000,
]

You can then call the query above using

Yii::$app->db->createCommand(
    'SET group_concat_max_len = :length', [
        ':length' => Yii::$app->params['group_concat_max_len']
    ]
)->execute();

Answer 2

You can execute any arbitrary query with Yii2 framework.

Use something like this for your issue:

// open connection to the DB
$connection = new \yii\db\Connection([
    'dsn' => $dsn,
    'username' => $username,
    'password' => $password,
]);
$connection->open();

// Update the MySQL config
$sql = 'SET group_concat_max_len = 50000;';
$connection->createCommand($sql)->execute();

Be aware that the DB answer will still be truncated if the length of your SELECT response is larger than 50000 (in my example, choose, the value best for you).

Be also aware that the effective maximum length of the return value is constrained by the value of max_allowed_packet of your MySQL server.

More on this MySQL configuration here: http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat

Answer 3

if you have used ActiveDataProvider , please try this code :

$dataProvider = new ActiveDataProvider([
        'query' => $query,



    ]);
    $dataProvider->db->createCommand('SET group_concat_max_len = 1024*4')->execute();