問題描述

使用 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.

這篇關于Yii2:如何使用聯合查詢和排序創建 ActiveDataProvider?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！