Yii2:如何使用联合查询和排序创建 ActiveDataProvider?

2021-12-24 00:00:00 sql union php yii2 gridview

使用 Yii 框架 2.0，我有两个数据库表，如下所示.

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

我想使用 ActiveDataProvider for GridView 查询这些记录，结果如下.

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

下面是我的纯 SQL 查询.

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

我想用 ActiveDataProvider 实现上述查询.我该怎么做?

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

推荐答案

这样做:

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

它的灵感来自这个例子在 Yii 指南中.

It is inspired by this example in the Yii guide.

相关文章