From 651c4c95b6f8f52d8d6feeeafcf41dd7e915956f Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Fri, 26 Jun 2020 02:22:27 -0400 Subject: [PATCH] APIv4 - Improve row_count to work with HAVING, GROUP BY, and SELECT This changes the meaning of $result->count(), to give a total count of filtered items, ignoring limit and offset. For DAO queries, you must select 'row_count' to trigger this behavior, as it requires a seperate query. --- CRM/Api4/Page/AJAX.php | 1 + Civi/Api4/Action/Domain/Get.php | 6 +- Civi/Api4/Generic/BasicGetAction.php | 2 +- Civi/Api4/Generic/BasicGetFieldsAction.php | 2 +- Civi/Api4/Generic/DAOGetAction.php | 29 ++++--- Civi/Api4/Generic/Result.php | 10 +-- .../Generic/Traits/ArrayQueryActionTrait.php | 12 +-- Civi/Api4/Query/Api4SelectQuery.php | 75 ++++++++++++------- ang/api4Explorer/Explorer.html | 4 +- ang/api4Explorer/Explorer.js | 45 +++++------ .../api/v4/Action/BasicActionsTest.php | 4 +- .../api/v4/Action/ComplexQueryTest.php | 23 +++++- .../phpunit/api/v4/Action/ContactGetTest.php | 19 ++++- .../api/v4/Action/GetFromArrayTest.php | 5 +- .../api/v4/Query/Api4SelectQueryTest.php | 34 +++++---- .../api/v4/Query/OptionValueJoinTest.php | 10 ++- 16 files changed, 182 insertions(+), 99 deletions(-) diff --git a/CRM/Api4/Page/AJAX.php b/CRM/Api4/Page/AJAX.php index 300fc33612..2e6854f9b0 100644 --- a/CRM/Api4/Page/AJAX.php +++ b/CRM/Api4/Page/AJAX.php @@ -129,6 +129,7 @@ class CRM_Api4_Page_AJAX extends CRM_Core_Page { foreach (get_class_vars(get_class($result)) as $key => $val) { $vals[$key] = $result->$key; } + unset($vals['rowCount']); $vals['count'] = $result->count(); return $vals; } diff --git a/Civi/Api4/Action/Domain/Get.php b/Civi/Api4/Action/Domain/Get.php index 22fded735d..0f8d12ca02 100644 --- a/Civi/Api4/Action/Domain/Get.php +++ b/Civi/Api4/Action/Domain/Get.php @@ -19,6 +19,8 @@ namespace Civi\Api4\Action\Domain; +use Civi\Api4\Generic\Result; + /** * @inheritDoc */ @@ -34,11 +36,11 @@ class Get extends \Civi\Api4\Generic\DAOGetAction { /** * @inheritDoc */ - protected function getObjects() { + protected function getObjects(Result $result) { if ($this->currentDomain) { $this->addWhere('id', '=', \CRM_Core_Config::domainID()); } - return parent::getObjects(); + parent::getObjects($result); } } diff --git a/Civi/Api4/Generic/BasicGetAction.php b/Civi/Api4/Generic/BasicGetAction.php index f1d34d237f..e40215faa7 100644 --- a/Civi/Api4/Generic/BasicGetAction.php +++ b/Civi/Api4/Generic/BasicGetAction.php @@ -59,7 +59,7 @@ class BasicGetAction extends AbstractGetAction { $this->expandSelectClauseWildcards(); $values = $this->getRecords(); $this->formatRawValues($values); - $result->exchangeArray($this->queryArray($values)); + $this->queryArray($values, $result); } /** diff --git a/Civi/Api4/Generic/BasicGetFieldsAction.php b/Civi/Api4/Generic/BasicGetFieldsAction.php index a277bf5901..070d9909bd 100644 --- a/Civi/Api4/Generic/BasicGetFieldsAction.php +++ b/Civi/Api4/Generic/BasicGetFieldsAction.php @@ -97,7 +97,7 @@ class BasicGetFieldsAction extends BasicGetAction { $values = $this->getRecords(); } $this->formatResults($values); - $result->exchangeArray($this->queryArray($values)); + $this->queryArray($values, $result); } /** diff --git a/Civi/Api4/Generic/DAOGetAction.php b/Civi/Api4/Generic/DAOGetAction.php index d922781ada..2b73f18a8f 100644 --- a/Civi/Api4/Generic/DAOGetAction.php +++ b/Civi/Api4/Generic/DAOGetAction.php @@ -71,20 +71,31 @@ class DAOGetAction extends AbstractGetAction { public function _run(Result $result) { $this->setDefaultWhereClause(); $this->expandSelectClauseWildcards(); - $result->exchangeArray($this->getObjects()); + $this->getObjects($result); } /** - * @return array|int + * @param \Civi\Api4\Generic\Result $result */ - protected function getObjects() { - $query = new Api4SelectQuery($this); - - $result = $query->run(); - if (is_array($result)) { - \CRM_Utils_API_HTMLInputCoder::singleton()->decodeRows($result); + protected function getObjects(Result $result) { + $getCount = in_array('row_count', $this->getSelect()); + $onlyCount = $this->getSelect() === ['row_count']; + + if (!$onlyCount) { + $query = new Api4SelectQuery($this); + $rows = $query->run(); + \CRM_Utils_API_HTMLInputCoder::singleton()->decodeRows($rows); + $result->exchangeArray($rows); + // No need to fetch count if we got a result set below the limit + if (!$this->getLimit() || count($rows) < $this->getLimit()) { + $result->rowCount = count($rows) + $this->getOffset(); + $getCount = FALSE; + } + } + if ($getCount) { + $query = new Api4SelectQuery($this); + $result->rowCount = $query->getCount(); } - return $result; } /** diff --git a/Civi/Api4/Generic/Result.php b/Civi/Api4/Generic/Result.php index 675b752e70..afc30c217a 100644 --- a/Civi/Api4/Generic/Result.php +++ b/Civi/Api4/Generic/Result.php @@ -40,6 +40,10 @@ class Result extends \ArrayObject implements \JsonSerializable { * @var int */ public $version = 4; + /** + * @var int + */ + public $rowCount; private $indexedBy; @@ -107,11 +111,7 @@ class Result extends \ArrayObject implements \JsonSerializable { * @return int */ public function count() { - $count = parent::count(); - if ($count == 1 && is_array($this->first()) && array_keys($this->first()) == ['row_count']) { - return $this->first()['row_count']; - } - return $count; + return $this->rowCount ?? parent::count(); } /** diff --git a/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php b/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php index fc7f21229b..08db9f75fa 100644 --- a/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php +++ b/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php @@ -32,16 +32,18 @@ trait ArrayQueryActionTrait { /** * @param array $values - * List of all rows - * @return array - * Filtered list of rows + * List of all rows to be filtered + * @param \Civi\Api4\Generic\Result $result + * Object to store result */ - protected function queryArray($values) { + protected function queryArray($values, $result) { $values = $this->filterArray($values); $values = $this->sortArray($values); + // Set total count before applying limit + $result->rowCount = count($values); $values = $this->limitArray($values); $values = $this->selectArray($values); - return $values; + $result->exchangeArray($values); } /** diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index 37a1780a44..b9bedfa86b 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -32,7 +32,8 @@ use Civi\Api4\Utils\SelectUtil; class Api4SelectQuery { const - MAIN_TABLE_ALIAS = 'a'; + MAIN_TABLE_ALIAS = 'a', + UNLIMITED = '18446744073709551615'; /** * @var \CRM_Utils_SQL_Select @@ -80,28 +81,31 @@ class Api4SelectQuery { */ public function __construct($apiGet) { $this->api = $apiGet; + // Always select ID of main table unless grouping by something else - $this->forceSelectId = !$apiGet->getGroupBy() || $apiGet->getGroupBy() === ['id']; - foreach ($apiGet->entityFields() as $field) { + $this->forceSelectId = !$this->getGroupBy() || $this->getGroupBy() === ['id']; + + // Build field lists + foreach ($this->api->entityFields() as $field) { $this->entityFieldNames[] = $field['name']; $field['sql_name'] = '`' . self::MAIN_TABLE_ALIAS . '`.`' . $field['column_name'] . '`'; $this->addSpecField($field['name'], $field); } - $baoName = CoreUtil::getBAOFromApiName($this->getEntity()); - $this->constructQueryObject(); + $tableName = CoreUtil::getTableName($this->getEntity()); + $this->query = \CRM_Utils_SQL_Select::from($tableName . ' ' . self::MAIN_TABLE_ALIAS); // Add ACLs first to avoid redundant subclauses + $baoName = CoreUtil::getBAOFromApiName($this->getEntity()); $this->query->where($this->getAclClause(self::MAIN_TABLE_ALIAS, $baoName)); } /** - * Builds final sql statement after all params are set. + * Builds main final sql statement after initialization. * * @return string * @throws \API_Exception * @throws \CRM_Core_Exception - * @throws \Civi\API\Exception\UnauthorizedException */ public function getSql() { // Add explicit joins. Other joins implied by dot notation may be added later @@ -118,7 +122,7 @@ class Api4SelectQuery { /** * Why walk when you can * - * @return array|int + * @return array */ public function run() { $results = []; @@ -126,10 +130,6 @@ class Api4SelectQuery { $this->debug('sql', $sql); $query = \CRM_Core_DAO::executeQuery($sql); while ($query->fetch()) { - if (in_array('row_count', $this->getSelect())) { - $results[]['row_count'] = (int) $query->c; - break; - } $result = []; foreach ($this->selectAliases as $alias => $expr) { $returnName = $alias; @@ -143,18 +143,45 @@ class Api4SelectQuery { } /** + * @return int * @throws \API_Exception */ - protected function buildSelectClause() { - $select = $this->getSelect(); + public function getCount() { + $this->addExplicitJoins(); + $this->buildWhereClause(); + // If no having or groupBy, we only need to select count + if (!$this->getHaving() && !$this->getGroupBy()) { + $this->query->select('COUNT(*) AS `c`'); + $sql = $this->query->toSQL(); + } + // Use a subquery to count groups from GROUP BY or results filtered by HAVING + else { + // With no HAVING, just select the last field grouped by + if (!$this->getHaving()) { + $select = array_slice($this->getGroupBy(), -1); + } + $this->buildSelectClause($select ?? NULL); + $this->buildHavingClause(); + $this->buildGroupBy(); + $subquery = $this->query->toSQL(); + $sql = "SELECT count(*) AS `c` FROM ( $subquery ) AS rows"; + } + $this->debug('sql', $sql); + return (int) \CRM_Core_DAO::singleValueQuery($sql); + } + + /** + * @param array $select + * Array of select expressions; defaults to $this->getSelect + * @throws \API_Exception + */ + protected function buildSelectClause($select = NULL) { + // Use default if select not provided, exclude row_count which is handled elsewhere + $select = array_diff($select ?? $this->getSelect(), ['row_count']); // An empty select is the same as * if (empty($select)) { $select = $this->entityFieldNames; } - elseif (in_array('row_count', $select)) { - $this->query->select("COUNT(*) AS `c`"); - return; - } else { if ($this->forceSelectId) { $select = array_merge(['id'], $select); @@ -251,7 +278,7 @@ class Api4SelectQuery { protected function buildLimit() { if ($this->getLimit() || $this->getOffset()) { // If limit is 0, mysql will actually return 0 results. Instead set to maximum possible. - $this->query->limit($this->getLimit() ?: '18446744073709551615', $this->getOffset()); + $this->query->limit($this->getLimit() ?: self::UNLIMITED, $this->getOffset()); } } @@ -653,16 +680,6 @@ class Api4SelectQuery { return $this->api->getCheckPermissions(); } - /** - * Get table name on basis of entity - * - * @return void - */ - public function constructQueryObject() { - $tableName = CoreUtil::getTableName($this->getEntity()); - $this->query = \CRM_Utils_SQL_Select::from($tableName . ' ' . self::MAIN_TABLE_ALIAS); - } - /** * @param string $path * @param array $field diff --git a/ang/api4Explorer/Explorer.html b/ang/api4Explorer/Explorer.html index a06a7ba5f6..5b47978683 100644 --- a/ang/api4Explorer/Explorer.html +++ b/ang/api4Explorer/Explorer.html @@ -50,10 +50,10 @@ -
+
select *
-
+
diff --git a/ang/api4Explorer/Explorer.js b/ang/api4Explorer/Explorer.js index 953bdba0ab..9e6b9644a8 100644 --- a/ang/api4Explorer/Explorer.js +++ b/ang/api4Explorer/Explorer.js @@ -287,14 +287,11 @@ }; $scope.selectRowCount = function() { - if ($scope.isSelectRowCount()) { - $scope.params.select = []; + var index = params.select.indexOf('row_count'); + if (index < 0) { + $scope.params.select.push('row_count'); } else { - $scope.params.select = ['row_count']; - $scope.index = ''; - if ($scope.params.limit == 25) { - $scope.params.limit = 0; - } + $scope.params.select.splice(index, 1); } }; @@ -308,7 +305,7 @@ }; function isSelectRowCount(params) { - return params && params.select && params.select.length === 1 && params.select[0] === 'row_count'; + return params && params.select && params.select.indexOf('row_count') >= 0; } function getEntity(entityName) { @@ -566,10 +563,6 @@ paramCount = _.size(params), i = 0; - if (isSelectRowCount(params)) { - results = result + 'Count'; - } - switch ($scope.selectedTab.code) { case 'js': case 'ang': @@ -606,9 +599,7 @@ // Write oop code code.oop = '$' + results + " = " + formatOOP(entity, action, params, 2) + "\n ->execute()"; - if (isSelectRowCount(params)) { - code.oop += "\n ->count()"; - } else if (_.isNumber(index)) { + if (_.isNumber(index)) { code.oop += !index ? '\n ->first()' : (index === -1 ? '\n ->last()' : '\n ->itemAt(' + index + ')'); } else if (index) { if (_.isString(index) || (_.isPlainObject(index) && !index[0] && !index['0'])) { @@ -619,7 +610,7 @@ } } code.oop += ";\n"; - if (!_.isNumber(index) && !isSelectRowCount(params)) { + if (!_.isNumber(index)) { code.oop += "foreach ($" + results + ' as $' + ((_.isString(index) && index) ? index + ' => $' : '') + result + ') {\n // do something\n}'; } break; @@ -661,9 +652,15 @@ } }); } else if (key === 'select') { - code += newLine; - // addSelect() is a variadic function & can take multiple arguments; selectRowCount() is a shortcut for addSelect('row_count') - code += isSelectRowCount(params) ? '->selectRowCount()' : '->addSelect(' + phpFormat(param).slice(1, -1) + ')'; + // selectRowCount() is a shortcut for addSelect('row_count') + if (isSelectRowCount(params)) { + code += newLine + '->selectRowCount()'; + param = _.without(param, 'row_count'); + } + // addSelect() is a variadic function & can take multiple arguments + if (param.length) { + code += newLine + '->addSelect(' + phpFormat(param).slice(1, -1) + ')'; + } } else if (key === 'chain') { _.each(param, function(chain, name) { code += newLine + "->addChain('" + name + "', " + formatOOP(chain[0], chain[1], chain[2], 2 + indent); @@ -711,12 +708,18 @@ $scope.loading = false; $scope.status = resp.data && resp.data.debug && resp.data.debug.log ? 'warning' : 'success'; $scope.debug = debugFormat(resp.data); - $scope.result = [formatMeta(resp.data), prettyPrintOne(_.escape(JSON.stringify(resp.data.values, null, 2)), 'js', 1)]; + $scope.result = [ + formatMeta(resp.data), + prettyPrintOne('(' + resp.data.values.length + ') ' + _.escape(JSON.stringify(resp.data.values, null, 2)), 'js', 1) + ]; }, function(resp) { $scope.loading = false; $scope.status = 'danger'; $scope.debug = debugFormat(resp.data); - $scope.result = [formatMeta(resp), prettyPrintOne(_.escape(JSON.stringify(resp.data, null, 2)))]; + $scope.result = [ + formatMeta(resp), + prettyPrintOne(_.escape(JSON.stringify(resp.data, null, 2))) + ]; }); }; diff --git a/tests/phpunit/api/v4/Action/BasicActionsTest.php b/tests/phpunit/api/v4/Action/BasicActionsTest.php index daf2b3470e..b71f2683a7 100644 --- a/tests/phpunit/api/v4/Action/BasicActionsTest.php +++ b/tests/phpunit/api/v4/Action/BasicActionsTest.php @@ -45,7 +45,9 @@ class BasicActionsTest extends UnitTestCase { MockBasicEntity::update()->addWhere('id', '=', $id2)->addValue('foo', 'new')->execute(); $result = MockBasicEntity::get()->addOrderBy('id', 'DESC')->setLimit(1)->execute(); - $this->assertCount(1, $result); + // The object's count() method will account for all results, ignoring limit, while the array results are limited + $this->assertCount(2, $result); + $this->assertCount(1, (array) $result); $this->assertEquals('new', $result->first()['foo']); $result = MockBasicEntity::save() diff --git a/tests/phpunit/api/v4/Action/ComplexQueryTest.php b/tests/phpunit/api/v4/Action/ComplexQueryTest.php index e859e144bd..b5b44dc8f1 100644 --- a/tests/phpunit/api/v4/Action/ComplexQueryTest.php +++ b/tests/phpunit/api/v4/Action/ComplexQueryTest.php @@ -23,6 +23,7 @@ namespace api\v4\Action; use api\v4\UnitTestCase; use Civi\Api4\Activity; +use Civi\Api4\Contact; /** * @group headless @@ -57,9 +58,27 @@ class ComplexQueryTest extends UnitTestCase { } /** - * Fetch all activities with a blue tag; and return all tags on the activities + * */ - public function testGetAllTagsForBlueTaggedActivities() { + public function testGetWithCount() { + $myName = uniqid('count'); + for ($i = 1; $i <= 20; ++$i) { + Contact::create() + ->addValue('first_name', "Contact $i") + ->addValue('last_name', $myName) + ->setCheckPermissions(FALSE)->execute(); + } + + $get1 = Contact::get() + ->addWhere('last_name', '=', $myName) + ->selectRowCount() + ->addSelect('first_name') + ->setLimit(10) + ->setDebug(TRUE) + ->setCheckPermissions(FALSE)->execute(); + + $this->assertEquals(20, $get1->count()); + $this->assertCount(10, (array) $get1); } diff --git a/tests/phpunit/api/v4/Action/ContactGetTest.php b/tests/phpunit/api/v4/Action/ContactGetTest.php index e1be3ca566..89730d5938 100644 --- a/tests/phpunit/api/v4/Action/ContactGetTest.php +++ b/tests/phpunit/api/v4/Action/ContactGetTest.php @@ -74,9 +74,22 @@ class ContactGetTest extends \api\v4\UnitTestCase { ->execute()->first(); $num = Contact::get()->setCheckPermissions(FALSE)->selectRowCount()->execute()->count(); - $this->assertCount($num - 1, Contact::get()->setCheckPermissions(FALSE)->setLimit(0)->setOffset(1)->execute()); - $this->assertCount($num - 2, Contact::get()->setCheckPermissions(FALSE)->setLimit(0)->setOffset(2)->execute()); - $this->assertCount(2, Contact::get()->setCheckPermissions(FALSE)->setLimit(2)->setOffset(0)->execute()); + + // The object's count() method will account for all results, ignoring limit & offset, while the array results are limited + $offset1 = Contact::get()->setCheckPermissions(FALSE)->setOffset(1)->execute(); + $this->assertCount($num, $offset1); + $this->assertCount($num - 1, (array) $offset1); + $offset2 = Contact::get()->setCheckPermissions(FALSE)->setOffset(2)->execute(); + $this->assertCount($num - 2, (array) $offset2); + $this->assertCount($num, $offset2); + // With limit, it doesn't fetch total count by default + $limit2 = Contact::get()->setCheckPermissions(FALSE)->setLimit(2)->execute(); + $this->assertCount(2, (array) $limit2); + $this->assertCount(2, $limit2); + // With limit, you have to trigger the full row count manually + $limit2 = Contact::get()->setCheckPermissions(FALSE)->setLimit(2)->addSelect('sort_name', 'row_count')->execute(); + $this->assertCount(2, (array) $limit2); + $this->assertCount($num, $limit2); } } diff --git a/tests/phpunit/api/v4/Action/GetFromArrayTest.php b/tests/phpunit/api/v4/Action/GetFromArrayTest.php index 74eae3c752..b900dca8bb 100644 --- a/tests/phpunit/api/v4/Action/GetFromArrayTest.php +++ b/tests/phpunit/api/v4/Action/GetFromArrayTest.php @@ -36,7 +36,10 @@ class GetFromArrayTest extends UnitTestCase { ->execute(); $this->assertEquals(3, $result[0]['field1']); $this->assertEquals(4, $result[1]['field1']); - $this->assertEquals(2, count($result)); + + // The object's count() method will account for all results, ignoring limit, while the array results are limited + $this->assertCount(2, (array) $result); + $this->assertCount(5, $result); } public function testArrayGetWithSort() { diff --git a/tests/phpunit/api/v4/Query/Api4SelectQueryTest.php b/tests/phpunit/api/v4/Query/Api4SelectQueryTest.php index a628b89106..d3bec5cd56 100644 --- a/tests/phpunit/api/v4/Query/Api4SelectQueryTest.php +++ b/tests/phpunit/api/v4/Query/Api4SelectQueryTest.php @@ -52,13 +52,13 @@ class Api4SelectQueryTest extends UnitTestCase { $phoneNum = $this->getReference('test_phone_1')['phone']; $contact = $this->getReference('test_contact_1'); - $api = \Civi\API\Request::create('Phone', 'get', ['version' => 4, 'checkPermissions' => FALSE]); + $api = \Civi\API\Request::create('Phone', 'get', [ + 'version' => 4, + 'checkPermissions' => FALSE, + 'select' => ['id', 'phone', 'contact.display_name', 'contact.first_name'], + 'where' => [['phone', '=', $phoneNum]], + ]); $query = new Api4SelectQuery($api); - $query->select[] = 'id'; - $query->select[] = 'phone'; - $query->select[] = 'contact.display_name'; - $query->select[] = 'contact.first_name'; - $query->where[] = ['phone', '=', $phoneNum]; $results = $query->run(); $this->assertCount(1, $results); @@ -67,20 +67,28 @@ class Api4SelectQueryTest extends UnitTestCase { } public function testInvaidSort() { - $api = \Civi\API\Request::create('Contact', 'get', ['version' => 4, 'checkPermissions' => FALSE]); + $api = \Civi\API\Request::create('Contact', 'get', [ + 'version' => 4, + 'checkPermissions' => FALSE, + 'select' => ['id', 'display_name'], + 'where' => [['first_name', '=', 'phoney']], + 'orderBy' => ['first_name' => 'sleep(1)'], + ]); $query = new Api4SelectQuery($api); - $query->select[] = 'id'; - $query->select[] = 'first_name'; - $query->select[] = 'phones.phone'; - $query->where[] = ['first_name', '=', 'Phoney']; - $query->orderBy = ['first_name' => 'sleep(1)']; try { $results = $query->run(); $this->fail('An Exception Should have been raised'); } catch (\API_Exception $e) { } - $query->orderBy = ['sleep(1)', 'ASC']; + $api = \Civi\API\Request::create('Contact', 'get', [ + 'version' => 4, + 'checkPermissions' => FALSE, + 'select' => ['id', 'display_name'], + 'where' => [['first_name', '=', 'phoney']], + 'orderBy' => ['sleep(1)' => 'ASC'], + ]); + $query = new Api4SelectQuery($api); try { $results = $query->run(); $this->fail('An Exception Should have been raised'); diff --git a/tests/phpunit/api/v4/Query/OptionValueJoinTest.php b/tests/phpunit/api/v4/Query/OptionValueJoinTest.php index eac88f25a0..39e0bd0141 100644 --- a/tests/phpunit/api/v4/Query/OptionValueJoinTest.php +++ b/tests/phpunit/api/v4/Query/OptionValueJoinTest.php @@ -48,11 +48,13 @@ class OptionValueJoinTest extends UnitTestCase { } public function testCommunicationMethodJoin() { - $api = \Civi\API\Request::create('Contact', 'get', ['version' => 4, 'checkPermissions' => FALSE]); + $api = \Civi\API\Request::create('Contact', 'get', [ + 'version' => 4, + 'checkPermissions' => FALSE, + 'select' => ['first_name', 'preferred_communication_method:label'], + 'where' => [['preferred_communication_method', 'IS NOT NULL']], + ]); $query = new Api4SelectQuery($api); - $query->select[] = 'first_name'; - $query->select[] = 'preferred_communication_method:label'; - $query->where[] = ['preferred_communication_method', 'IS NOT NULL']; $results = $query->run(); $first = array_shift($results); $keys = array_keys($first); -- 2.25.1