CRM_Utils_SQL_Select - Allow construction of UNION/INTERSECT with sub-queries
authorTim Otten <totten@civicrm.org>
Wed, 14 Jun 2023 08:50:23 +0000 (01:50 -0700)
committerTim Otten <totten@civicrm.org>
Wed, 14 Jun 2023 09:18:20 +0000 (02:18 -0700)
commit1e8a7c49c98888898edecf3c80697554410d11be
tree9fef3e09ed4bdc6c622d4f2f7c6d002e492433ad
parent51ec12f5c890cce5fecf393d60d5a3921aa9eff6
CRM_Utils_SQL_Select - Allow construction of UNION/INTERSECT with sub-queries

MySQL 5.7 supports "UNION" (which combines the result-sets from two queries). MySQL 8.0
and MariaDB 10.3 expand this more set-operations (eg "INTERSECT", "EXCEPT").

The patch allows CRM_Utils_SQL_Select to construct a range of queries with these
operators.

Before
------

Not supported

After
-----

A few examples:

```php
CRM_Utils_SQL_Select::fromSet()->union([$subQuery1, $subQuery2])

CRM_Utils_SQL_Select::fromSet()
  ->union($subQuery1)
  ->union($subQuery2);

CRM_Utils_SQL_Select::fromSet()
  ->setOp('UNION ALL', [$subQuery1, $subQuery2])
  ->setOp('EXCEPT', [$subQuery3]);
```

Comments
--------

In these examples, we build one top-level `SELECT` query, and then
use a sub-query to get the UNION/INTERSECT/EXCEPT data. This is not
strictly necessary. (SQL allows set-ops without a top-level SELECT.)
However, this pattern is powerful - because you can freely mix-in
other subclauses (`WHERE`/`GROUP BY`/`HAVING`/etc).

I'm not 100% certain that these signatures are best. Looking at the MySQL
BNF, this is still a bit reductive wrt precedence of the UNION / EXCEPT / INTERSECT
operations. However, `CRM_Utils_SQL_Select` is already reductive about precedence
of WHEREs.

Part of me wanted to model the UNION/setops as a separate object-type
(independent of SELECT) -- and then improve subquery support for SELECT, eg

```
$union = new CRM_Utils_SQL_SetOp('UNION', [$subQuery1, $subQuery2]);
$select = CRM_Utils_SQL_Select::from($union)->where('...');
```

However, in the CiviMail PR that uses UNION, there's an existing `Select`
object where they iteratively mix-in more union clauses. It seems that the
`Select::fromSet()->union()` might be a bit more amenable.
CRM/Utils/SQL/Select.php
tests/phpunit/CRM/Utils/SQL/SelectTest.php