php - query - Yii2: ¿Cómo crear ActiveDataProvider con consulta y clasificación de unión?

yii2 like

Question

Con Yii framework 2.0 tengo dos tablas de base de datos como las siguientes.

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

Me gustaría consultar estos registros usando ActiveDataProvider para GridView y el resultado es el siguiente.

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

Debajo está mi consulta SQL pura de trabajo.

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 

Me gustaría implementar esta consulta anterior con ActiveDataProvider. ¿Cómo puedo hacer eso?

Answer 1

No lo he probado. Pero debería funcionar así, creo:

$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();

Debería crear una consulta que se parece a esto:

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`

Está inspirado en este ejemplo en la guía Yii.

Answer 2

$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,              
]);

Esto debería funcionar, parece haber un error que no permite el orden correcto de las consultas de UNION.