Hello, how can I mount this query with Cakephp 3?
SELECT DISTINCT campo FROM (
SELECT `var1` campo FROM tabla WHERE `var1` is not null UNION ALL
SELECT `var2` campo FROM tabla WHERE `var2` is not null ) T ORDER BY campo
I think it’s something like this:
$var1 = TableRegistry::getTableLocator()->get('Tabla')
->select(['Tabla.var1'])
->where(['Tabla.var1 is not null']);
$var2 = TableRegistry::getTableLocator()->get('Tabla')
->select(['Tabla.var2'])
->where(['Tabla.var2 is not null']);
$unir = $var1->unionAll($var2);
$campo = TableRegistry::getTableLocator()->get('Tabla')->find()
->distinct()
->from([Tabla->alias() => $unir])
->order(['Tabla.campo' => 'DESC']);
It is a single table and combines two fields into one, sorts them and removes repeating or null records.