From 1f76bc105d6beef17ea226e7a48f49e6f280371b Mon Sep 17 00:00:00 2001 From: Patrick Figel Date: Sat, 4 Sep 2021 14:51:42 +0200 Subject: [PATCH] Add SqlFunction support in addWhere This adds an optional $isExpression parameter in addWhere that allows use of SqlFunctions in WHERE expressions: Contact::get(FALSE) ->addWhere('last_name', '=', $last_name) ->addWhere('LOWER(first_name)', '=', "BINARY('ALICE')", TRUE) ->execute()->indexBy('id'); This also adds a few tests to the existing expression support for ON and HAVING clauses as well as a new BINARY function. --- Civi/Api4/Generic/DAOGetAction.php | 16 ++++++++ Civi/Api4/Query/Api4SelectQuery.php | 7 ++-- Civi/Api4/Query/SqlFunctionBINARY.php | 37 +++++++++++++++++++ .../phpunit/api/v4/Action/ContactGetTest.php | 20 ++++++++++ tests/phpunit/api/v4/Action/FkJoinTest.php | 15 ++++++++ .../phpunit/api/v4/Action/SqlFunctionTest.php | 14 +++++++ 6 files changed, 106 insertions(+), 3 deletions(-) create mode 100644 Civi/Api4/Query/SqlFunctionBINARY.php diff --git a/Civi/Api4/Generic/DAOGetAction.php b/Civi/Api4/Generic/DAOGetAction.php index f374047486..aedc6362ca 100644 --- a/Civi/Api4/Generic/DAOGetAction.php +++ b/Civi/Api4/Generic/DAOGetAction.php @@ -123,6 +123,22 @@ class DAOGetAction extends AbstractGetAction { } } + /** + * @param string $fieldName + * @param string $op + * @param mixed $value + * @param bool $isExpression + * @return $this + * @throws \API_Exception + */ + public function addWhere(string $fieldName, string $op, $value = NULL, bool $isExpression = FALSE) { + if (!in_array($op, CoreUtil::getOperators())) { + throw new \API_Exception('Unsupported operator'); + } + $this->where[] = [$fieldName, $op, $value, $isExpression]; + return $this; + } + /** * @return array */ diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index 119abe49a9..3f44491bde 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -431,7 +431,7 @@ class Api4SelectQuery { /** * Validate and transform a leaf clause array to SQL. - * @param array $clause [$fieldName, $operator, $criteria] + * @param array $clause [$fieldName, $operator, $criteria, $isExpression] * @param string $type * WHERE|HAVING|ON * @param int $depth @@ -443,12 +443,13 @@ class Api4SelectQuery { $field = NULL; // Pad array for unary operators [$expr, $operator, $value] = array_pad($clause, 3, NULL); + $isExpression = $clause[3] ?? FALSE; if (!in_array($operator, CoreUtil::getOperators(), TRUE)) { throw new \API_Exception('Illegal operator'); } // For WHERE clause, expr must be the name of a field. - if ($type === 'WHERE') { + if ($type === 'WHERE' && !$isExpression) { $field = $this->getField($expr, TRUE); FormattingUtil::formatInputValue($value, $expr, $field, $operator); $fieldAlias = $this->getExpression($expr)->render($this->apiFieldSpec); @@ -491,7 +492,7 @@ class Api4SelectQuery { } $fieldAlias = '`' . $fieldAlias . '`'; } - elseif ($type === 'ON') { + elseif ($type === 'ON' || ($type === 'WHERE' && $isExpression)) { $expr = $this->getExpression($expr); $fieldName = count($expr->getFields()) === 1 ? $expr->getFields()[0] : NULL; $fieldAlias = $expr->render($this->apiFieldSpec); diff --git a/Civi/Api4/Query/SqlFunctionBINARY.php b/Civi/Api4/Query/SqlFunctionBINARY.php new file mode 100644 index 0000000000..e2500d83db --- /dev/null +++ b/Civi/Api4/Query/SqlFunctionBINARY.php @@ -0,0 +1,37 @@ + FALSE, + 'must_be' => ['SqlField', 'SqlString'], + ], + ]; + } + + /** + * @return string + */ + public static function getTitle(): string { + return ts('Binary'); + } + +} diff --git a/tests/phpunit/api/v4/Action/ContactGetTest.php b/tests/phpunit/api/v4/Action/ContactGetTest.php index 2e5075ea03..b47a69480d 100644 --- a/tests/phpunit/api/v4/Action/ContactGetTest.php +++ b/tests/phpunit/api/v4/Action/ContactGetTest.php @@ -261,6 +261,26 @@ class ContactGetTest extends \api\v4\UnitTestCase { $this->assertEquals(['Student'], $result['Contact_RelationshipCache_Contact_01.contact_sub_type:label']); } + public function testGetWithWhereExpression() { + $last_name = uniqid(__FUNCTION__); + + $alice = Contact::create() + ->setValues(['first_name' => 'Alice', 'last_name' => $last_name]) + ->execute()->first(); + + $result = Contact::get(FALSE) + ->addWhere('last_name', '=', $last_name) + ->addWhere('LOWER(first_name)', '=', "BINARY('ALICE')", TRUE) + ->execute()->indexBy('id'); + $this->assertCount(0, $result); + + $result = Contact::get(FALSE) + ->addWhere('last_name', '=', $last_name) + ->addWhere('LOWER(first_name)', '=', "BINARY('alice')", TRUE) + ->execute()->indexBy('id'); + $this->assertArrayHasKey($alice['id'], (array) $result); + } + /** * @throws \API_Exception */ diff --git a/tests/phpunit/api/v4/Action/FkJoinTest.php b/tests/phpunit/api/v4/Action/FkJoinTest.php index d7d95d5f8b..960b638ad4 100644 --- a/tests/phpunit/api/v4/Action/FkJoinTest.php +++ b/tests/phpunit/api/v4/Action/FkJoinTest.php @@ -426,4 +426,19 @@ class FkJoinTest extends UnitTestCase { $this->assertStringContainsString("Deprecated join alias 'contact' used in APIv4 get. Should be changed to 'contact_id'", $message); } + public function testJoinWithExpression() { + Phone::create(FALSE) + ->setValues(['contact_id' => $this->getReference('test_contact_1')['id'], 'phone' => '654321']) + ->execute(); + $contacts = Contact::get(FALSE) + ->addSelect('id', 'phone.phone') + ->addJoin('Phone', 'INNER', ['LOWER(phone.phone)', '=', "CONCAT('6', '5', '4', '3', '2', '1')"]) + ->addWhere('id', 'IN', [$this->getReference('test_contact_1')['id'], $this->getReference('test_contact_2')['id']]) + ->addOrderBy('phone.id') + ->execute(); + $this->assertCount(1, $contacts); + $this->assertEquals($this->getReference('test_contact_1')['id'], $contacts[0]['id']); + $this->assertEquals('654321', $contacts[0]['phone.phone']); + } + } diff --git a/tests/phpunit/api/v4/Action/SqlFunctionTest.php b/tests/phpunit/api/v4/Action/SqlFunctionTest.php index 5cf7373770..ebac8d5d30 100644 --- a/tests/phpunit/api/v4/Action/SqlFunctionTest.php +++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php @@ -131,6 +131,20 @@ class SqlFunctionTest extends UnitTestCase { $this->assertEquals(100, $result[0]['MIN:total_amount']); $this->assertEquals(2, $result[0]['count']); $this->assertEquals(1, $result[1]['count']); + + $result = Contribution::get(FALSE) + ->addGroupBy('contact_id') + ->addGroupBy('receive_date') + ->addSelect('contact_id') + ->addSelect('receive_date') + ->addSelect('SUM(total_amount)') + ->addOrderBy('receive_date') + ->addWhere('contact_id', '=', $cid) + ->addHaving('SUM(total_amount)', '>', 300) + ->execute(); + $this->assertCount(1, $result); + $this->assertStringStartsWith('2020-04-04', $result[0]['receive_date']); + $this->assertEquals(400, $result[0]['SUM:total_amount']); } public function testComparisonFunctions() { -- 2.25.1