From 4f0729ed39e193f46ed75f8fccfbafb983b68d03 Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Thu, 3 Dec 2020 19:28:00 -0500
Subject: [PATCH] Search Ext: Support robust joins in UI
This uses better join metadata to give explicit conditions for each join,
removing the ambiguity when more than one type of join is possible between the same 2 entities.
It also adds support for joins via EntityBridge APIs.
---
ext/search/CRM/Search/Page/Admin.php | 17 ---
ext/search/Civi/Search/Admin.php | 142 ++++++++++++++++--
ext/search/ang/crmSearchAdmin.ang.php | 1 +
ext/search/ang/crmSearchAdmin.module.js | 28 +++-
.../ang/crmSearchAdmin/compose/criteria.html | 2 +-
.../crmSearchAdmin.component.js | 79 ++++++----
.../crmSearchAdmin/searchList.controller.js | 2 +-
7 files changed, 211 insertions(+), 60 deletions(-)
diff --git a/ext/search/CRM/Search/Page/Admin.php b/ext/search/CRM/Search/Page/Admin.php
index ccd4cf6b24..5ab061f1dc 100644
--- a/ext/search/CRM/Search/Page/Admin.php
+++ b/ext/search/CRM/Search/Page/Admin.php
@@ -21,23 +21,6 @@ class CRM_Search_Page_Admin extends CRM_Core_Page {
];
CRM_Utils_System::appendBreadCrumb([$breadCrumb]);
- $schema = \Civi\Search\Admin::getSchema();
-
- // If user does not have permission to search any entity, bye bye.
- if (!$schema) {
- CRM_Utils_System::permissionDenied();
- }
-
- // Add client-side vars for the search UI
- $vars = [
- 'schema' => $schema,
- 'links' => \Civi\Search\Admin::getLinks(array_column($schema, 'name')),
- ];
-
- Civi::resources()
- ->addBundle('bootstrap3')
- ->addVars('search', $vars);
-
// Load angular module
$loader = new Civi\Angular\AngularLoader();
$loader->setPageName('civicrm/admin/search');
diff --git a/ext/search/Civi/Search/Admin.php b/ext/search/Civi/Search/Admin.php
index 00448fee40..c338156f00 100644
--- a/ext/search/Civi/Search/Admin.php
+++ b/ext/search/Civi/Search/Admin.php
@@ -21,7 +21,10 @@ class Admin {
* @return array
*/
public static function getAdminSettings():array {
+ $schema = self::getSchema();
return [
+ 'schema' => $schema,
+ 'joins' => self::getJoins(array_column($schema, NULL, 'name')),
'operators' => \CRM_Utils_Array::makeNonAssociative(self::getOperators()),
'functions' => \CRM_Api4_Page_Api4Explorer::getSqlFunctions(),
'displayTypes' => Display::getDisplayTypes(['name', 'label', 'description', 'icon']),
@@ -58,7 +61,7 @@ class Admin {
public static function getSchema() {
$schema = [];
$entities = \Civi\Api4\Entity::get()
- ->addSelect('name', 'title', 'type', 'title_plural', 'description', 'icon', 'paths')
+ ->addSelect('name', 'title', 'type', 'title_plural', 'description', 'icon', 'paths', 'dao', 'bridge')
->addWhere('searchable', '=', TRUE)
->addOrderBy('title_plural')
->setChain([
@@ -93,7 +96,7 @@ class Admin {
'action' => $action,
];
}
- $entity['fields'] = civicrm_api4($entity['name'], 'getFields', [
+ $entity['fields'] = (array) civicrm_api4($entity['name'], 'getFields', [
'select' => $getFields,
'where' => [['name', 'NOT IN', ['api_key', 'hash']]],
'orderBy' => ['label'],
@@ -114,20 +117,133 @@ class Admin {
* @param array $allowedEntities
* @return array
*/
- public static function getLinks(array $allowedEntities) {
- $results = [];
- $keys = array_flip(['alias', 'entity', 'joinType']);
- foreach (civicrm_api4('Entity', 'getLinks', ['where' => [['entity', 'IN', $allowedEntities]]], ['entity' => 'links']) as $entity => $links) {
- $entityLinks = [];
- foreach ($links as $link) {
- if (!empty($link['entity']) && in_array($link['entity'], $allowedEntities)) {
- // Use entity.alias as array key to avoid duplicates
- $entityLinks[$link['entity'] . $link['alias']] = array_intersect_key($link, $keys);
+ public static function getJoins(array $allowedEntities) {
+ $joins = [];
+ foreach ($allowedEntities as $entity) {
+ if (!empty($entity['dao'])) {
+ /* @var \CRM_Core_DAO $daoClass */
+ $daoClass = $entity['dao'];
+ $references = $daoClass::getReferenceColumns();
+ // Only the first bridge reference gets processed, so if it's dynamic we want to be sure it's first in the list
+ usort($references, function($reference) {
+ return is_a($reference, 'CRM_Core_Reference_Dynamic') ? -1 : 1;
+ });
+ $fields = array_column($entity['fields'], NULL, 'name');
+ $bridge = in_array('EntityBridge', $entity['type']) ? $entity['name'] : NULL;
+ $baseEntity = $bridge && isset($entity['bridge'][1]) ? $allowedEntities[$fields[$entity['bridge'][1]]['fk_entity']] ?? NULL : NULL;
+ if ($bridge && !$baseEntity) {
+ continue;
+ }
+ foreach ($references as $reference) {
+ $keyField = $fields[$reference->getReferenceKey()] ?? NULL;
+ // Exclude any joins that are better represented by pseudoconstants
+ if (is_a($reference, 'CRM_Core_Reference_OptionValue')
+ || !$keyField || !empty($keyField['options'])
+ // Limit bridge joins to just the first
+ || $bridge && array_search($keyField['name'], $entity['bridge']) !== 0
+ // Sanity check - table should match
+ || $daoClass::getTableName() !== $reference->getReferenceTable()
+ ) {
+ continue;
+ }
+ // Dynamic references use a column like "entity_table"
+ $dynamicCol = $reference->getTypeColumn();
+ if ($dynamicCol) {
+ $targetTables = $daoClass::buildOptions($dynamicCol);
+ if (!$targetTables) {
+ continue;
+ }
+ $targetTables = array_keys($targetTables);
+ }
+ else {
+ $targetTables = [$reference->getTargetTable()];
+ }
+ foreach ($targetTables as $targetTable) {
+ $targetDao = \CRM_Core_DAO_AllCoreTables::getClassForTable($targetTable);
+ $targetEntityName = \CRM_Core_DAO_AllCoreTables::getBriefName($targetDao);
+ if (!isset($allowedEntities[$targetEntityName]) || $targetEntityName === $entity['name']) {
+ continue;
+ }
+ $targetEntity = $allowedEntities[$targetEntityName];
+ if (!$bridge) {
+ // Add the straight 1-1 join
+ $alias = $entity['name'] . '_' . $targetEntityName . '_' . $keyField['name'];
+ $joins[$entity['name']][] = [
+ 'label' => $entity['title'] . ' ' . $targetEntity['title'],
+ 'description' => $dynamicCol ? '' : $keyField['label'],
+ 'entity' => $targetEntityName,
+ 'conditions' => self::getJoinConditions($keyField['name'], $alias . '.' . $reference->getTargetKey(), $targetTable, $dynamicCol),
+ 'alias' => $alias,
+ 'multi' => FALSE,
+ ];
+ // Flip the conditions & add the reverse (1-n) join
+ $alias = $targetEntityName . '_' . $entity['name'] . '_' . $keyField['name'];
+ $joins[$targetEntityName][] = [
+ 'label' => $targetEntity['title'] . ' ' . $entity['title_plural'],
+ 'description' => $dynamicCol ? '' : $keyField['label'],
+ 'entity' => $entity['name'],
+ 'conditions' => self::getJoinConditions($reference->getTargetKey(), $alias . '.' . $keyField['name'], $targetTable, $dynamicCol ? $alias . '.' . $dynamicCol : NULL),
+ 'alias' => $alias,
+ 'multi' => TRUE,
+ ];
+ }
+ else {
+ // Add joins for the two entities that connect through this bridge (n-n)
+ $symmetric = $baseEntity['name'] === $targetEntityName;
+ $targetsTitle = $symmetric ? $allowedEntities[$bridge]['title_plural'] : $targetEntity['title_plural'];
+ $joins[$baseEntity['name']][] = [
+ 'label' => $baseEntity['title'] . ' ' . $targetsTitle,
+ 'description' => ts('Multiple %1 per %2', [1 => $targetsTitle, 2 => $baseEntity['title']]),
+ 'entity' => $targetEntityName,
+ 'conditions' => [$bridge],
+ 'bridge' => $bridge,
+ 'alias' => $baseEntity['name'] . "_{$bridge}_" . $targetEntityName,
+ 'multi' => TRUE,
+ ];
+ if (!$symmetric) {
+ $joins[$targetEntityName][] = [
+ 'label' => $targetEntity['title'] . ' ' . $baseEntity['title_plural'],
+ 'description' => ts('Multiple %1 per %2', [1 => $baseEntity['title_plural'], 2 => $targetEntity['title']]),
+ 'entity' => $baseEntity['name'],
+ 'conditions' => [$bridge],
+ 'bridge' => $bridge,
+ 'alias' => $targetEntityName . "_{$bridge}_" . $baseEntity['name'],
+ 'multi' => TRUE,
+ ];
+ }
+ }
+ }
}
}
- $results[$entity] = array_values($entityLinks);
}
- return array_filter($results);
+ return $joins;
+ }
+
+ /**
+ * Boilerplate join clause
+ *
+ * @param string $nearCol
+ * @param string $farCol
+ * @param string $targetTable
+ * @param string|null $dynamicCol
+ * @return array[]
+ */
+ private static function getJoinConditions($nearCol, $farCol, $targetTable, $dynamicCol) {
+ $conditions = [
+ [
+ $nearCol,
+ '=',
+ $farCol,
+ ],
+ ];
+ if ($dynamicCol) {
+ $conditions[] = [
+ $dynamicCol,
+ '=',
+ "'$targetTable'",
+ ];
+ }
+ return $conditions;
}
}
diff --git a/ext/search/ang/crmSearchAdmin.ang.php b/ext/search/ang/crmSearchAdmin.ang.php
index 9176b14b29..a947d51b30 100644
--- a/ext/search/ang/crmSearchAdmin.ang.php
+++ b/ext/search/ang/crmSearchAdmin.ang.php
@@ -12,6 +12,7 @@ return [
'partials' => [
'ang/crmSearchAdmin',
],
+ 'bundles' => ['bootstrap3'],
'basePages' => ['civicrm/admin/search'],
'requires' => ['crmUi', 'crmUtil', 'ngRoute', 'ui.sortable', 'ui.bootstrap', 'api4', 'crmSearchActions', 'crmSearchKit', 'crmRouteBinder'],
'settingsFactory' => ['\Civi\Search\Admin', 'getAdminSettings'],
diff --git a/ext/search/ang/crmSearchAdmin.module.js b/ext/search/ang/crmSearchAdmin.module.js
index e7a8d24efb..90d6570598 100644
--- a/ext/search/ang/crmSearchAdmin.module.js
+++ b/ext/search/ang/crmSearchAdmin.module.js
@@ -3,6 +3,7 @@
// Shared between router and searchMeta service
var searchEntity,
+ joinIndex,
undefined;
// Declare module and route/controller/services
@@ -81,15 +82,33 @@
})
.factory('searchMeta', function() {
+ // JoinIndex lists each join by alias. It gets built once then cached.
+ function _getJoinIndex() {
+ if (!joinIndex) {
+ joinIndex = _.transform(CRM.crmSearchAdmin.joins, function(joinIndex, joins, base) {
+ _.each(joins, function(join) {
+ join.base = base;
+ joinIndex[join.alias] = join;
+ });
+ });
+ }
+ return joinIndex;
+ }
function getEntity(entityName) {
if (entityName) {
- return _.find(CRM.vars.search.schema, {name: entityName});
+ return _.find(CRM.crmSearchAdmin.schema, {name: entityName});
}
}
+ function getJoin(fullNameOrAlias) {
+ var joinIndex = _getJoinIndex(),
+ alias = _.last(fullNameOrAlias.split(' AS '));
+ return joinIndex[alias];
+ }
function getField(fieldName, entityName) {
var dotSplit = fieldName.split('.'),
joinEntity = dotSplit.length > 1 ? dotSplit[0] : null,
name = _.last(dotSplit).split(':')[0],
+ join,
field;
// Custom fields contain a dot in their fieldname
// If 3 segments, the first is the joinEntity and the last 2 are the custom field
@@ -105,9 +124,13 @@
}
}
if (joinEntity) {
- entityName = _.find(CRM.vars.search.links[entityName], {alias: joinEntity}).entity;
+ join = getJoin(joinEntity);
+ entityName = getJoin(joinEntity).entity;
}
field = _.find(getEntity(entityName).fields, {name: name});
+ if (!field && join && join.bridge) {
+ field = _.find(getEntity(join.bridge).fields, {name: name});
+ }
if (field) {
field.entity = entityName;
return field;
@@ -136,6 +159,7 @@
return {
getEntity: getEntity,
getField: getField,
+ getJoin: getJoin,
parseExpr: parseExpr,
getDefaultLabel: function(col) {
var info = parseExpr(col),
diff --git a/ext/search/ang/crmSearchAdmin/compose/criteria.html b/ext/search/ang/crmSearchAdmin/compose/criteria.html
index 6b5eb4b3af..3e9629aeb4 100644
--- a/ext/search/ang/crmSearchAdmin/compose/criteria.html
+++ b/ext/search/ang/crmSearchAdmin/compose/criteria.html
@@ -8,7 +8,7 @@
<select class="form-control" ng-model="join[1]" ng-options="o.k as o.v for o in ::joinTypes" ></select>
</div>
<fieldset class="api4-clause-fieldset">
- <crm-search-clause clauses="join" format="json" skip="2" op="AND" label="{{ ts('If') }}" fields="fieldsForWhere" ></crm-search-clause>
+ <crm-search-clause clauses="join" format="json" skip="2 + getJoin(join[0]).conditions.length" op="AND" label="{{ ts('If') }}" fields="fieldsForWhere" ></crm-search-clause>
</fieldset>
</fieldset>
<fieldset>
diff --git a/ext/search/ang/crmSearchAdmin/crmSearchAdmin.component.js b/ext/search/ang/crmSearchAdmin/crmSearchAdmin.component.js
index 1b29fe45c6..e3ff102b18 100644
--- a/ext/search/ang/crmSearchAdmin/crmSearchAdmin.component.js
+++ b/ext/search/ang/crmSearchAdmin/crmSearchAdmin.component.js
@@ -29,7 +29,7 @@
$scope.groupOptions = CRM.crmSearchActions.groupOptions;
// Try to create a sensible list of entities one might want to search for,
// excluding those whos primary purpose is to provide joins or option lists to other entities
- var primaryEntities = _.filter(CRM.vars.search.schema, function(entity) {
+ var primaryEntities = _.filter(CRM.crmSearchAdmin.schema, function(entity) {
return !_.includes(entity.type, 'EntityBridge') && !_.includes(entity.type, 'OptionList');
});
$scope.entities = formatForSelect2(primaryEntities, 'name', 'title_plural', ['description', 'icon']);
@@ -190,14 +190,16 @@
}
};
+ $scope.getJoin = searchMeta.getJoin;
+
$scope.getJoinEntities = function() {
- var joinEntities = _.transform(CRM.vars.search.links[ctrl.savedSearch.api_entity], function(joinEntities, link) {
- var entity = searchMeta.getEntity(link.entity);
+ var joinEntities = _.transform(CRM.crmSearchAdmin.joins[ctrl.savedSearch.api_entity], function(joinEntities, join) {
+ var entity = searchMeta.getEntity(join.entity);
if (entity) {
joinEntities.push({
- id: link.entity + ' AS ' + link.alias,
- text: entity.title_plural,
- description: '(' + link.alias + ')',
+ id: join.entity + ' AS ' + join.alias,
+ description: join.description,
+ text: join.label,
icon: entity.icon
});
}
@@ -210,7 +212,12 @@
$timeout(function() {
if ($scope.controls.join) {
ctrl.savedSearch.api_params.join = ctrl.savedSearch.api_params.join || [];
- ctrl.savedSearch.api_params.join.push([$scope.controls.join, false]);
+ var join = searchMeta.getJoin($scope.controls.join),
+ params = [$scope.controls.join, false];
+ _.each(_.cloneDeep(join.conditions), function(condition) {
+ params.push(condition);
+ });
+ ctrl.savedSearch.api_params.join.push(params);
loadFieldOptions();
}
$scope.controls.join = '';
@@ -648,33 +655,49 @@
}
function getAllFields(suffix, disabledIf) {
- function formatFields(entityName, prefix) {
- return _.transform(searchMeta.getEntity(entityName).fields, function(result, field) {
- var item = {
- id: prefix + field.name + (field.options ? suffix : ''),
- text: field.label,
- description: field.description
- };
- if (disabledIf(item.id)) {
- item.disabled = true;
- }
- result.push(item);
- }, []);
+ function formatFields(entityName, join) {
+ var prefix = join ? join.alias + '.' : '',
+ result = [];
+
+ function addFields(fields) {
+ _.each(fields, function(field) {
+ var item = {
+ id: prefix + field.name + (field.options ? suffix : ''),
+ text: field.label,
+ description: field.description
+ };
+ if (disabledIf(item.id)) {
+ item.disabled = true;
+ }
+ result.push(item);
+ });
+ }
+
+ // Add extra searchable fields from bridge entity
+ if (join && join.bridge) {
+ addFields(_.filter(searchMeta.getEntity(join.bridge).fields, function(field) {
+ return (field.name !== 'id' && field.name !== 'entity_id' && field.name !== 'entity_table' && !field.fk_entity);
+ }));
+ }
+
+ addFields(searchMeta.getEntity(entityName).fields);
+ return result;
}
var mainEntity = searchMeta.getEntity(ctrl.savedSearch.api_entity),
result = [{
text: mainEntity.title_plural,
icon: mainEntity.icon,
- children: formatFields(ctrl.savedSearch.api_entity, '')
+ children: formatFields(ctrl.savedSearch.api_entity)
}];
_.each(ctrl.savedSearch.api_params.join, function(join) {
- var joinName = join[0].split(' AS '),
- joinEntity = searchMeta.getEntity(joinName[0]);
+ var joinInfo = searchMeta.getJoin(join[0]),
+ joinEntity = searchMeta.getEntity(joinInfo.entity);
result.push({
- text: joinEntity.title_plural + ' (' + joinName[1] + ')',
+ text: joinInfo.label,
+ description: joinInfo.description,
icon: joinEntity.icon,
- children: formatFields(joinEntity.name, joinName[1] + '.')
+ children: formatFields(joinEntity.name, joinInfo)
});
});
return result;
@@ -702,11 +725,15 @@
enqueue(mainEntity);
}
_.each(ctrl.savedSearch.api_params.join, function(join) {
- var joinName = join[0].split(' AS '),
- joinEntity = searchMeta.getEntity(joinName[0]);
+ var joinInfo = searchMeta.getJoin(join[0]),
+ joinEntity = searchMeta.getEntity(joinInfo.entity),
+ bridgeEntity = joinInfo.bridge ? searchMeta.getEntity(joinInfo.bridge) : null;
if (typeof joinEntity.optionsLoaded === 'undefined') {
enqueue(joinEntity);
}
+ if (bridgeEntity && typeof bridgeEntity.optionsLoaded === 'undefined') {
+ enqueue(bridgeEntity);
+ }
});
if (!_.isEmpty(entities)) {
crmApi4(entities).then(function(results) {
diff --git a/ext/search/ang/crmSearchAdmin/searchList.controller.js b/ext/search/ang/crmSearchAdmin/searchList.controller.js
index 01c27da97f..105647f718 100644
--- a/ext/search/ang/crmSearchAdmin/searchList.controller.js
+++ b/ext/search/ang/crmSearchAdmin/searchList.controller.js
@@ -5,7 +5,7 @@
var ts = $scope.ts = CRM.ts(),
ctrl = $scope.$ctrl = this;
this.savedSearches = savedSearches;
- this.entityTitles = _.transform(CRM.vars.search.schema, function(titles, entity) {
+ this.entityTitles = _.transform(CRM.crmSearchAdmin.schema, function(titles, entity) {
titles[entity.name] = entity.title_plural;
}, {});
--
2.25.1