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

Question

With Yii framework 2.0 I have two database tables as following.

A table:
   a_id = 1, name = yes, number = 123
   a_id = 2, name = no, number = 456 
   a_id = 3, name = ok,  number = 683

B table:
  id = 1, a_id = 1, firstname = s
  id = 2, a_id = 1, firstname = y
  id = 3, a_id = 2, firstname = e
  id = 4, a_id = 2, firstname = x
  id = 5, a_id = 2, firstname = t
  id = 6, a_id = 3, firstname = r

I would like to query these records using ActiveDataProvider for GridView and have the result as following.

a_id = 1, name = yes, number = 123
a_id = 1, name = s, number = null
a_id = 1, name = y, number = null
a_id = 2, name = no, number = 456
a_id = 2, name = e, number = null
a_id = 2, name = x, number = null
a_id = 2, name = t, number = null
a_id = 3, name = ok,  number = 683
a_id = 3, name = r, number = null

Below is my working pure SQL query.

SELECT `a_id`, `name`, `number` FROM `user` WHERE number != ''
UNION ALL
SELECT `a_id`, `firstname` as name , null as `number` FROM `customer` 
WHERE `firstname` != ''
ORDER BY `a_id` ASC, name ASC 

I would like to implement this above query with ActiveDataProvider. How can I do that?

Answer 1

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.

Answer 2

$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.

Answer 3

The following code was tested in my model "CoinsHistorySearch.php", hope to help some people:

$queryHistory = CoinsHistory::find();
$queryHistoryRecent = CoinsHistoryRecent::find();
$query = (new ActiveQuery(CoinsHistory::className()))->from([
    'union_history' => $queryHistory->union($queryHistoryRecent)
]);

$dataProvider = new ActiveDataProvider([
    'query' => $query,
    'sort' => ['defaultOrder' => ['id' => SORT_DESC]]
]);

$filter = [];
$queryHistory->andFilterWhere($filter);
$queryHistoryRecent->andFilterWhere($filter);

Answer 4

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 here

return $dataProvider;

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