Yii2: How to create ActiveDataProvider with union query and sorting?

This will do it:

$query1 = (new \yii\db\Query())    ->select("a_id, name, number")    ->from('user')    ->where(['!=', 'number', '']);$query2 = (new \yii\db\Query())    ->select("a_id, firstname as name , null as number")    ->from('customer')    ->where(['!=', 'firstname', '']);$unionQuery = (new \yii\db\Query())    ->from(['dummy_name' => $query1->union($query2)])    ->orderBy(['a_id' => SORT_ASC, 'name' => SORT_ASC]);$provider = new ActiveDataProvider([    'query' => $unionQuery,    'pagination' => [        'pageSize' => 20,    ],]);$rows = $provider->getModels();

It should create a query that looks like this:

SELECT * FROM (    (SELECT `a_id`, `name`, `number` FROM `user` WHERE `number` != ''   )    UNION     (SELECT `a_id`, `firstname` AS `name`, `null` AS `number` FROM `customer` WHERE `firstname` != '')) `dummy_name`ORDER BY `a_id`, `name`

It is inspired by this example in the Yii guide.

This might be helpful for someone

// Query Table A$tableA = (new \yii\db\Query())         ->select("a_id, name, number")         ->from('user')         ->where(['!=', 'number', '']);// Query table B$tableB = (new \yii\db\Query())         ->select("a_id, firstname as name , null as number")         ->from('customer')         ->where(['!=', 'firstname', '']);// Union table A and B$tableA->union($tableB);/* * Table A is your Model * find() method in activeRecord will load the instance of ActiveQuery * Now you can use base Query methods like select and from on find() method */$query = TableA::find()->select('*')->from(['random_name' => $tableA]);// Dataprovider$dataProvider = new ActiveDataProvider([   'query' => $query,   'pagination' => [      'pageSize' => 20,    ],   'sort'=> ['defaultOrder' => ['a_id' => SORT_ASC]],]);//Search filters and grid filters can go herereturn $dataProvider;

All your search filters, and relations in the grid should work with this method.

$query1 = (new \yii\db\Query())    ->select("a_id, name, number")    ->from('user')    ->where(['!=', 'number', '']);$query2 = (new \yii\db\Query())    ->select("a_id, firstname as name , null as number")    ->from('customer')    ->where(['!=', 'firstname', '']);$query1->union($query2, false);//false is UNION, true is UNION ALL$sql = $query1->createCommand()->getRawSql();$sql .= ' ORDER BY id DESC';$query = User::findBySql($sql);$dataProvider = new ActiveDataProvider([    'query' => $query,              ]);

This should work, there seems to be a bug that doesn't allow proper ordering of UNION queries.