CRM_Contact_BAO_Query
class CRM_Contact_BAO_Query
This is the heart of the search query building mechanism.
Constants
NO_RETURN_PROPERTIES |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
MODE_CONTACTS |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
MODE_CONTRIBUTE |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
MODE_MEMBER |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
MODE_EVENT |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
MODE_GRANT |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
MODE_PLEDGEBANK |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
MODE_PLEDGE |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
MODE_CASE |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
MODE_ACTIVITY |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
MODE_CAMPAIGN |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
MODE_MAILING |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
MODE_ALL |
The various search modes. As of February 2017, entries not present for 4, 32, 64, 1024. MODE_ALL seems to be out of sync with the available constants; if this is intentionally excluding MODE_MAILING then that may bear documenting? Likewise if there's reason for the missing modes (4, 32, 64 etc). |
Properties
static array | $_defaultReturnProperties | The default set of return properties. | |
static array | $_defaultHierReturnProperties | The default set of hier return properties. | |
array | $_params | The set of input params. | |
$_cfIDs | |||
$_paramLookup | |||
$_sort | |||
array | $_returnProperties | The set of output params | |
array | $_select | The select clause | |
array | $_element | The name of the elements that are in the select clause used to extract the values. | |
array | $_tables | The tables involved in the query. | |
array | $_whereTables | The table involved in the where clause. | |
array | $_where | Array of WHERE clause components. | |
string | $_whereClause | The WHERE clause as a string. | |
string | $_permissionWhereClause | Additional WHERE clause for permissions. | |
string | $_fromClause | The from string | |
string | $_permissionFromClause | Additional permission from clause | |
string | $_simpleFromClause | The from clause for the simple select and alphabetical select | |
string | $_having | The having values | |
array | $_qill | The english language version of the query | |
array | $_fields | All the fields that could potentially be involved in this query | |
array | $_options | The cache to translate the option values into labels. | |
boolean | $_search | Are we in search mode. | |
boolean | $_skipPermission | Should we skip permission checking. | |
boolean | $_skipDeleteClause | Should we skip adding of delete clause. | |
boolean | $_strict | Are we in strict mode (use equality over LIKE) | |
string | $_operator | What operator to use to group the clauses. | |
$_mode | |||
boolean | $_primaryLocation | Should we only search on primary location. | |
boolean | $_includeContactIds | Are contact ids part of the query. | |
boolean | $_smartGroupCache | Should we use the smart group cache. | |
string | $_displayRelationshipType | Should we display contacts with a specific relationship type. | |
Object | $_customQuery | Reference to the query object for custom values. | |
boolean | $_useDistinct | Should we enable the distinct clause, used if we are including more than one group | |
$_useGroupBy | Should we just display one contact record | ||
static array | $_relType | The relationship type direction | |
static array | $_activityRole | The activity role | |
static array | $_considerCompActivities | Consider the component activity type during activity search. | |
static array | $_withContactActivitiesOnly | Consider with contact activities only, during activity search. | |
string | $_distinctComponentClause | Use distinct component clause for component searches | |
$_rowCountClause | |||
string | $_groupByComponentClause | Use groupBy component clause for component searches | |
static array | $_openedPanes | Track open panes, useful in advance search | |
array | $_locationSpecificCustomFields | For search builder - which custom fields are location-dependent | |
static array | $_dependencies | The tables which have a dependency on location and/or address | |
static | $_locationSpecificFields | List of location specific fields. | |
protected | $_rangeCache | Remember if we handle either end of a number or date range so we can skip the other | |
protected Boolean | $_relationshipValuesAdded | Set to true when $this->relationship is run to avoid adding twice | |
static String | $_relationshipTempTable | Set to the name of the temp table if one has been created | |
$_pseudoConstantsSelect |
Methods
Class constructor which also does all the work.
Function which actually does all the work for the constructor.
Function for same purpose as convertFormValues.
Some composite fields do not appear in the fields array hack to make them part of the query.
Given a list of conditions in params and a list of desired return Properties generate the required select and from clauses. Note that since the where clause introduces new tables, the initial attempt also retrieves all variables used in the params list
If the return Properties are set in a hierarchy, traverse the hierarchy to get the return values.
If the return Properties are set in a hierarchy, traverse the hierarchy to get the return values.
Generate the query based on what type of query we need.
Get where values from the parameters.
Fix date values.
Convert values from form-appropriate to query-object appropriate.
Function to support legacy format for groups and tags.
Fix values from query from/to something no-one cared enough to document.
Get the where clause for a single field.
Given a list of conditions in params generate the required where clause.
Generate where clause for any parameters not already handled.
No description
Getter for tables array.
Sometimes used to create the from clause, but, not reliably, set this AND set tables.
Generate the where clause (used in match contacts and permissions)
Create the from clause.
WHERE / QILL clause for deleted_contacts
Where / qill clause for contact_type
Where / qill clause for contact_sub_type.
No description
Where / qill clause for groups.
Function translates selection of group type into a list of groups.
No description
Where / qill clause for cms users
All tag search specific.
Where / qill clause for tag.
Where/qill clause for notes
No description
Where / qill clause for sort_name
Where/qill clause for greeting fields.
Where / qill clause for email
Where / qill clause for phone number
Where / qill clause for phone type/location
Where / qill clause for street_address.
Where / qill clause for street_unit.
Where / qill clause for sorting by character.
Where / qill clause for including contact ids.
Where / qill clause for postal code.
Where / qill clause for location type.
No description
Where / qill clause for county (if present).
Where / qill clause for state/province AND country (if present).
Where / qill clause for change log.
No description
No description
No description
No description
No description
Where / qill clause for relationship.
Add start & end date criteria in
Default set of return properties.
Get primary condition for a sql clause.
Wrapper for a simple search query.
These are stub comments as this function needs more explanation - particularly in terms of how it relates to $this->searchQuery and why it replicates rather than calles $this->searchQuery.
Get the actual custom field name by stripping off the appended string.
Convert submitted values for relative custom fields to query object format.
Are we dealing with custom field of type date.
Has this field already been reformatting to Query object syntax.
If the state and country are passed remove state.
For some special cases, grouping by subset of select fields becomes mandatory.
Include Select columns in groupBy clause.
Create and query the db for an contact search.
Fetch a list of contacts from the prev/next cache for displaying a search results page
Populate $this->_permissionWhereClause with permission related clause and update other query related properties.
No description
No description
Getter for the qill object.
Default set of return default hier return properties.
Build query for a date field.
No description
No description
Calculate date from age.
Given the field name, operator, value & its data type builds the where Clause for the query used for handling 'IS NULL'/'IS NOT NULL' operators
No description
No description
No description
No description
See CRM-19811 for why this is database hurty without apparent benefit.
No description
Builds the necessary structures for all fields that are similar to option value look-ups.
Check and explode a user defined numeric string into an array this was the protocol used by search builder in the old old days before we had super nice js widgets to do the hard work
Convert the pseudo constants id's to their names
Include pseudo fields LEFT JOIN.
Build qill for field.
Alter value to reflect wildcard settings.
Process special fields of Search Form in OK (Operator in Key) format
Parse and assimilate the various sort options.
Convert a string of group IDs to a string of group labels.
Set the qill and where properties for a field.
Details
at line 426
CRM_Contact_BAO_Query
__construct(array $params = NULL, array $returnProperties = NULL, array $fields = NULL, bool $includeContactIds = FALSE, bool $strict = FALSE, bool|int $mode = 1, bool $skipPermission = FALSE, bool $searchDescendentGroups = TRUE, bool $smartGroupCache = TRUE, null $displayRelationshipType = NULL, string $operator = 'AND', string $apiEntity = NULL)
Class constructor which also does all the work.
at line 489
initialize(string $apiEntity = NULL)
Function which actually does all the work for the constructor.
at line 553
buildParamsLookup()
Function for same purpose as convertFormValues.
Like convert form values this function exists to pre-Process parameters from the form.
It is unclear why they are different functions & likely relates to advances search versus search builder.
The direction we are going is having the form convert values to a standardised format & moving away from weird & wonderful where clause switches.
Fix and handle contact deletion nicely.
this code is primarily for search builder use case where different clauses can specify if they want deleted.
CRM-11971
at line 621
addSpecialFields($apiEntity)
Some composite fields do not appear in the fields array hack to make them part of the query.
at line 653
selectClause(string $apiEntity = NULL)
Given a list of conditions in params and a list of desired return Properties generate the required select and from clauses. Note that since the where clause introduces new tables, the initial attempt also retrieves all variables used in the params list
at line 997
addHierarchicalElements()
If the return Properties are set in a hierarchy, traverse the hierarchy to get the return values.
at line 1326
addMultipleElements()
If the return Properties are set in a hierarchy, traverse the hierarchy to get the return values.
at line 1361
array
query(bool $count = FALSE, bool $sortByChar = FALSE, bool $groupContacts = FALSE, bool $onlyDeleted = FALSE)
Generate the query based on what type of query we need.
at line 1493
mixed
getWhereValues(string $name, mixed $grouping)
Get where values from the parameters.
at line 1511
static
fixDateValues(bool $relative, string $from, string $to)
Fix date values.
at line 1550
static array
convertFormValues(array $formValues, int $wildcard, bool $useEquals = FALSE, string $apiEntity = NULL, array $entityReferenceFields = array())
Convert values from form-appropriate to query-object appropriate.
The query object is increasingly supporting the sql-filter syntax which is the most flexible syntax. So, ideally we would convert all fields to look like array( 0 => $fieldName // Set the operator for legacy reasons, but it is ignored 1 => '=' // array in sql filter syntax 2 => array('BETWEEN' => array(1,60), 3 => null 4 => null );
There are some examples of the syntax in https://github.com/civicrm/civicrm-core/tree/master/api/v3/examples/Relationship
More notes at CRM_Core_DAO::createSQLFilter
and a list of supported operators in CRM_Core_DAO
at line 1675
static
legacyConvertFormValues(string $id, array|int $values)
Function to support legacy format for groups and tags.
at line 1703
static array|null
fixWhereValues(int $id, array $values, int $wildcard, bool $useEquals = FALSE, string $apiEntity = NULL)
Fix values from query from/to something no-one cared enough to document.
at line 1778
whereClauseSingle(array $values, string $apiEntity = NULL)
Get the where clause for a single field.
at line 2016
string
whereClause(string $apiEntity = NULL)
Given a list of conditions in params generate the required where clause.
at line 2084
restWhere(array $values)
Generate where clause for any parameters not already handled.
at line 2358
static array
getLocationTableName($where, $locType)
at line 2431
array
store(CRM_Core_DAO $dao)
Given a result dao, extract the values and return that array
at line 2476
array
tables()
Getter for tables array.
at line 2491
array
whereTables()
Sometimes used to create the from clause, but, not reliably, set this AND set tables.
It's unclear the intent - there is a 'simpleFrom' clause which takes whereTables into account & a fromClause which doesn't.
logic may have eroded?
at line 2506
static string
getWhereClause(array $params, array $fields, array $tables, $whereTables, bool $strict = FALSE)
Generate the where clause (used in match contacts and permissions)
at line 2544
static string
fromClause(array $tables, array $inner = NULL, array $right = NULL, bool $primaryLocation = TRUE, int $mode = 1, string|NULL $apiEntity = NULL)
Create the from clause.
at line 2797
deletedContacts(array $values)
WHERE / QILL clause for deleted_contacts
at line 2810
contactType($values)
Where / qill clause for contact_type
at line 2875
contactSubType(array $values)
Where / qill clause for contact_sub_type.
at line 2885
includeContactSubTypes($value, $grouping, string $op = 'LIKE')
at line 2923
group($values)
Where / qill clause for groups.
at line 3030
array
getGroupsFromTypeCriteria($value)
Function translates selection of group type into a list of groups.
at line 3047
null|string
addGroupContactCache(array $groups, string $tableAlias = NULL, string $joinTable = "contact_a", string $op)
at line 3097
ufUser($values)
Where / qill clause for cms users
at line 3118
tagSearch(array $values)
All tag search specific.
at line 3180
tag(array $values)
Where / qill clause for tag.
at line 3261
notes(array $values)
Where/qill clause for notes
at line 3308
bool
nameNullOrEmptyOp(string $name, $op, $grouping)
at line 3336
sortName(array $values)
Where / qill clause for sort_name
at line 3412
greetings(array $values)
Where/qill clause for greeting fields.
at line 3427
protected
email(array $values, string $apiEntity)
Where / qill clause for email
at line 3471
phone_numeric(array $values)
Where / qill clause for phone number
at line 3491
phone_option_group(array $values)
Where / qill clause for phone type/location
at line 3506
street_address(array $values)
Where / qill clause for street_address.
at line 3539
street_number(array $values)
Where / qill clause for street_unit.
at line 3572
sortByCharacter(array $values)
Where / qill clause for sorting by character.
at line 3584
includeContactIDs()
Where / qill clause for including contact ids.
at line 3606
postalCode(array $values)
Where / qill clause for postal code.
at line 3655
string
locationType(array $values, null $status = NULL)
Where / qill clause for location type.
at line 3686
array|NULL
country($values, bool $fromStateProvince = TRUE)
at line 3734
string
county(array $values, null $status = NULL)
Where / qill clause for county (if present).
at line 3803
string
stateProvince(array $values, null $status = NULL)
Where / qill clause for state/province AND country (if present).
at line 3834
changeLog(array $values)
Where / qill clause for change log.
at line 3853
modifiedDates($values)
at line 3876
demographics($values)
at line 3903
privacy($values)
at line 3923
privacyOptions($values)
at line 3963
preferredCommunication($values)
at line 3990
relationship(array $values)
Where / qill clause for relationship.
at line 4174
addRelationshipDateClauses(string $grouping, array $where)
Add start & end date criteria in
at line 4205
static array
defaultReturnProperties(int $mode = 1)
Default set of return properties.
at line 4287
static string|NULL
getPrimaryCondition(int $value)
Get primary condition for a sql clause.
at line 4304
static string
getQuery(array $params = NULL, array $returnProperties = NULL, bool $count = FALSE)
Wrapper for a simple search query.
at line 4342
static array
apiQuery(array $params = NULL, array $returnProperties = NULL, null $fields = NULL, string $sort = NULL, int $offset, int $row_count = 25, bool $smartGroupCache = TRUE, bool $count = FALSE, bool $skipPermissions = TRUE, int $mode = CRM_Contact_BAO_Query::MODE_CONTACTS, string $apiEntity = NULL)
These are stub comments as this function needs more explanation - particularly in terms of how it relates to $this->searchQuery and why it replicates rather than calles $this->searchQuery.
This function was originally written as a wrapper for the api query but is called from multiple places in the core code directly so the name is misleading. This function does not use the searchQuery function but it is unclear as to whehter that is historical or there is a reason CRM-11290 led to the permissioning action being extracted from searchQuery & shared with this function
at line 4441
static string
getCustomFieldName(string $parameterName)
Get the actual custom field name by stripping off the appended string.
The string could be _relative, _from, or _to
at line 4467
static protected
convertCustomRelativeFields(array $formValues, array $params, string $values, string $fieldName)
Convert submitted values for relative custom fields to query object format.
The query will support the sqlOperator format so convert to that format.
at line 4519
static bool
isCustomDateField($fieldName)
Are we dealing with custom field of type date.
at line 4543
static bool;
isAlreadyProcessedForQueryFormat(mixed $values)
Has this field already been reformatting to Query object syntax.
The form layer passed formValues to this function in preProcess & postProcess. Reason unknown. This seems to come with associated double queries & is possibly damaging performance.
However, here we add a tested function to ensure convertFormValues identifies pre-processed fields & returns them as they are.
at line 4563
static
filterCountryFromValuesIfStateExists(array $formValues)
If the state and country are passed remove state.
Country is implicit from the state, but including both results in a poor query as there is no combined index on state AND country.
CRM-18125
at line 4586
static string
appendAnyValueToSelect(array $selectClauses, array $groupBy)
For some special cases, grouping by subset of select fields becomes mandatory.
Hence, full_group_by mode is handled by appending any_value keyword to select fields not present in groupBy
at line 4619
static string
getGroupByFromSelectColumns(array $selectClauses, array $groupBy = NULL)
Include Select columns in groupBy clause.
at line 4693
CRM_Core_DAO
searchQuery(int $offset, int $rowCount, string|CRM_Utils_Sort $sort = NULL, bool $count = FALSE, bool $includeContactIds = FALSE, bool $sortByChar = FALSE, bool $groupContacts = FALSE, bool $returnQuery = FALSE, string $additionalWhereClause = NULL, null $sortOrder = NULL, string $additionalFromClause = NULL, bool $skipOrderAndLimit = FALSE)
Create and query the db for an contact search.
at line 4815
CRM_Core_DAO
getCachedContacts(string $cacheKey, int $offset, int $rowCount, bool $includeContactIds)
Fetch a list of contacts from the prev/next cache for displaying a search results page
at line 4843
generatePermissionClause(bool $onlyDeleted = FALSE, bool $count = FALSE)
Populate $this->_permissionWhereClause with permission related clause and update other query related properties.
Function calls ACL permission class and hooks to filter the query appropriately
Note that these 2 params were in the code when extracted from another function and a second round extraction would be to make them properties of the class
at line 4885
setSkipPermission($val)
at line 4894
array
summaryContribution(null $context = NULL)
at line 5054
string
qill()
Getter for the qill object.
at line 5063
static array
defaultHierReturnProperties()
Default set of return default hier return properties.
at line 5151
dateQueryBuilder(array $values, string $tableName, string $fieldName, string $dbFieldName, string $fieldTitle, bool $appendTimeStamp = TRUE)
Build query for a date field.
at line 5280
numberRangeBuilder($values, string $tableName, string $fieldName, string $dbFieldName, $fieldTitle, null $options = NULL)
at line 5363
ageRangeQueryBuilder($values, string $tableName, string $fieldName, string $dbFieldName, $fieldTitle, null $options = NULL)
at line 5447
static string
calcDateFromAge(string $asofDate, int $age, string $type)
Calculate date from age.
at line 5478
static string
buildClause(string $field, string $op, string $value = NULL, string $dataType = NULL)
Given the field name, operator, value & its data type builds the where Clause for the query used for handling 'IS NULL'/'IS NOT NULL' operators
at line 5550
array
openedSearchPanes(bool $reset = FALSE)
at line 5584
setOperator($operator)
at line 5595
string
getOperator()
at line 5604
filterRelatedContacts($from, $where, $having)
at line 5686
static bool
caseImportant($op)
See CRM-19811 for why this is database hurty without apparent benefit.
at line 5697
static bool
componentPresent($returnProperties, $prefix)
at line 5727
optionValueQuery(string $name, string $op, string $value, int $grouping, string $daoName = NULL, array $field, string $label, string $dataType = 'String', bool $useIDsOnly = FALSE)
Builds the necessary structures for all fields that are similar to option value look-ups.
at line 5791
static bool|array
parseSearchBuilderString(string $string, string $dataType = 'Integer')
Check and explode a user defined numeric string into an array this was the protocol used by search builder in the old old days before we had super nice js widgets to do the hard work
at line 5830
array|NULL
convertToPseudoNames(CRM_Core_DAO $dao, bool $return = FALSE, bool $usedForAPI = FALSE)
Convert the pseudo constants id's to their names
at line 5955
array|NULL
includePseudoFieldsJoin(string|array $sort)
Include pseudo fields LEFT JOIN.
at line 6015
static array
buildQillForFieldValue(string $daoName, string $fieldName, mixed $fieldValue, string $op, array $pseudoExtraParam = array(), int $type = CRM_Utils_Type::T_STRING)
Build qill for field.
Qill refers to the query detail visible on the UI.
at line 6115
static string
getWildCardedValue(bool $wildcard, string $op, string $value)
Alter value to reflect wildcard settings.
The form will have tried to guess whether this is a good field to wildcard but there is also a site-wide setting that specifies whether it is OK to append the wild card to the beginning or only the end of the string
at line 6138
static
processSpecialFormValue(array $formValues, array $specialFields, array $changeNames = array())
Process special fields of Search Form in OK (Operator in Key) format
at line 6184
protected array
prepareOrderBy(string|CRM_Utils_Sort $sort, bool $sortByChar, null $sortOrder, string $additionalFromClause)
Parse and assimilate the various sort options.
Side-effect: if sorting on a common column from a related table (city
, postal_code
,
email
), the related table may be joined automatically.
At time of writing, this code is deeply flawed and should be rewritten. For the moment, it's been extracted to a standalone function.
at line 6323
string
convertGroupIDStringToLabelString(CRM_Core_DAO $dao, string $val)
Convert a string of group IDs to a string of group labels.
The original string may include duplicates and groups the user does not have permission to see.
at line 6343
setQillAndWhere(string $name, string $op, string|array $value, string $grouping, string $field)
Set the qill and where properties for a field.
This function is intended as a short-term function to encourage refactoring & re-use - but really we should just have less special-casing.