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?

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

Solution 2

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.

Solution 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);

Solution 4

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

View more solutions

Comments

[robsch]

Does table B have column id or a_id?

[O Connor]

I tried your above code, but it orders only the records of each table, table per table, means it first orders all the records of the user table and then just the customer table. Sample result of a_id ordering based on your code and my sample database above: 1,2,3,1,1,2,2,2,3. When I used the sort function of the GridView it is still the same. What I would like to approach is 1,1,1,2,2,2,2,3,3. Please help!

[robsch]

@OConnor I've updated my answer. As you can see I moved the where clause. Does this work?

[robsch]

@OConnor Changed it once again. Now a subquery is used. I think this is required. Alternative would be to use createCommand() to build a raw SQL statement where you could place you SQL code directly.