From 1e8a7c49c98888898edecf3c80697554410d11be Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Wed, 14 Jun 2023 01:50:23 -0700 Subject: [PATCH] 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 | 62 ++++++++++++++++++++++ tests/phpunit/CRM/Utils/SQL/SelectTest.php | 26 +++++++++ 2 files changed, 88 insertions(+) diff --git a/CRM/Utils/SQL/Select.php b/CRM/Utils/SQL/Select.php index 94452cf946..2f32fdb437 100644 --- a/CRM/Utils/SQL/Select.php +++ b/CRM/Utils/SQL/Select.php @@ -72,6 +72,7 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery { private $onDuplicates = []; private $selects = []; private $from; + private $setOps; private $joins = []; private $wheres = []; private $groupBys = []; @@ -93,6 +94,18 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery { return new self($from, $options); } + /** + * Create a new SELECT-like query where. + * + * @param array $options + * @return CRM_Utils_SQL_Select + */ + public static function fromSet($options = []) { + $result = new self(NULL, $options); + $result->setOps = []; + return $result; + } + /** * Create a partial SELECT query. * @@ -330,6 +343,47 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery { return $this; } + /** + * Add a union to the list of set operations. + * + * Ex: CRM_Utils_SQL_Select::fromSet()->union([$subQuery1, $subQuery2]) + * Ex: CRM_Utils_SQL_Select::fromSet()->union($subQuery1)->union($subQuery2); + * + * @param string|array|\CRM_Utils_SQL_Select $subQueries + * @return $this + */ + public function union($subQueries) { + return $this->setOp('UNION', $subQueries); + } + + /** + * Add a set operation. + * + * Ex: CRM_Utils_SQL_Select::fromSet()->setOp('INTERSECT', [$subQuery1, $subQuery2]) + * + * @param string $setOperation + * Ex: 'UNION', 'UNION ALL', 'INTERSECT', 'EXCEPT' + * NOTE: The query-builder supports any set-operation. However, MySQL 5.7 only supports UNION. + * @param string|array|\CRM_Utils_SQL_Select $subQueries + * @return $this + * @see https://dev.mysql.com/doc/refman/8.0/en/set-operations.html + */ + public function setOp(string $setOperation, $subQueries) { + if ($this->from !== NULL || !is_array($this->setOps)) { + throw new CRM_Core_Exception("Set-operation ($setOperation) must have a list of subqueries. Primitive FROM is not supported."); + } + $subQueries = is_array($subQueries) ? $subQueries : [$subQueries]; /* Simple (array)cast would mishandle objects. */ + foreach ($subQueries as $subQuery) { + if ($this->setOps === []) { + $this->setOps[] = ['', $subQuery]; + } + else { + $this->setOps[] = [" $setOperation ", $subQuery]; + } + } + return $this; + } + /** * Insert the results of the SELECT query into another * table. @@ -495,6 +549,14 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery { if ($this->from !== NULL) { $sql .= 'FROM ' . $this->from . "\n"; } + elseif (is_array($this->setOps)) { + $sql .= 'FROM ('; + foreach ($this->setOps as $setOp) { + $sql .= $setOp[0]; + $sql .= '(' . (is_object($setOp[1]) ? $setOp[1]->toSQL() : $setOp[1]) . ')'; + } + $sql .= ")\n"; + } foreach ($this->joins as $join) { $sql .= $join . "\n"; } diff --git a/tests/phpunit/CRM/Utils/SQL/SelectTest.php b/tests/phpunit/CRM/Utils/SQL/SelectTest.php index 734371e1df..931b1ab33b 100644 --- a/tests/phpunit/CRM/Utils/SQL/SelectTest.php +++ b/tests/phpunit/CRM/Utils/SQL/SelectTest.php @@ -308,6 +308,32 @@ class CRM_Utils_SQL_SelectTest extends CiviUnitTestCase { $this->assertLike('SELECT c, d, a, b, e FROM foo WHERE (c = "4") AND (a = 2) AND (whipit())', $query->toSQL()); } + public function testUnion() { + $a = CRM_Utils_SQL_Select::from('a')->select('a_name')->where('a1 = !num')->param('num', 100); + $b = CRM_Utils_SQL_Select::from('b')->select('b_name')->where('b2 = @val')->param('val', "ab cd"); + $u = CRM_Utils_SQL_Select::fromSet()->union([$a, $b])->limit(100)->orderBy('a_name'); + $expectA = 'SELECT a_name FROM a WHERE (a1 = 100) '; + $expectB = 'SELECT b_name FROM b WHERE (b2 = "ab cd") '; + $expectUnion = "SELECT * FROM (($expectA) UNION ($expectB)) ORDER BY a_name LIMIT 100 OFFSET 0"; + $this->assertLike($expectUnion, $u->toSQL()); + } + + public function testUnionIntersect() { + $a = CRM_Utils_SQL_Select::from('a')->select('a_name')->where('a1 = !num')->param('num', 100); + $b = CRM_Utils_SQL_Select::from('b')->select('b_name')->where('b2 = @val')->param('val', "bb bb"); + $c = CRM_Utils_SQL_Select::from('c')->select('c_name')->where('c3 = @val')->param('val', "cc cc"); + $u = CRM_Utils_SQL_Select::fromSet() + ->union([$a, $b]) + ->setOp('INTERSECT', $c) + ->limit(100) + ->orderBy('a_name'); + $expectA = 'SELECT a_name FROM a WHERE (a1 = 100) '; + $expectB = 'SELECT b_name FROM b WHERE (b2 = "bb bb") '; + $expectC = 'SELECT c_name FROM c WHERE (c3 = "cc cc") '; + $expectUnion = "SELECT * FROM (($expectA) UNION ($expectB) INTERSECT ($expectC)) ORDER BY a_name LIMIT 100 OFFSET 0"; + $this->assertLike($expectUnion, $u->toSQL()); + } + public function testArrayGet() { $select = CRM_Utils_SQL_Select::from("foo") ->param('hello', 'world'); -- 2.25.1