Yii2：如何使用联合查询和排序创建ActiveDataProvider？ [英] Yii2: How to create ActiveDataProvider with union query and sorting?

问题描述

使用Yii framework 2.0我有两个数据库表，如下所示。

 一张表：
 a_id = 1， name = yes，number = 123 
 a_id = 2，name = no，number = 456 
 a_id = 3，name = ok，number = 683 
 
 B表：
 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为GridView查询这些记录，并得到如下结果。

  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 = nu ll 
  

以下是我的工作纯SQL查询。

  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实现上述查询。我怎么做到这一点？

解决方案

我没有测试过它。但它应该像这样工作，我认为：

  $ query1 =（new \yii\db\Query（） ）
  - > select（a_id，name，number）
  - > from（'user'）
  - >其中（['！='，'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（）; 
  

它应该创建一个如下所示的查询：

$ b $ SELECT * FROM
（
（SELECT`a_id`，`name`，`number` FROM`user` WHERE）b

 ``````=''）
 UNION 
（SELECT`a_id`，`firstname` as`name`，`null` as`number` FROM`customer` WHERE`firstname`！='' ）
）`dummy_name` 
 ORDER BY`a_id`，`name` 
  

它的灵感来自这个例子在Yii指导。

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?

解决方案

I have not tested it. But it should work like this, I think:

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

这篇关于Yii2：如何使用联合查询和排序创建ActiveDataProvider？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！