php - 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 yiidbQuery())
    ->select("a_id, name, number")
    ->from('user')
    ->where(['!=', 'number', '']);

$query2 = (new yiidbQuery())
    ->select("a_id, firstname as name , null as number")
    ->from('customer')
    ->where(['!=', 'firstname', '']);

$unionQuery = (new yiidbQuery())
    ->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.