Type: New Feature
Affects Version/s: None
Fix Version/s: 1.5
This feature will provide an alternate interface for creating and saving search queries. Users will "construct" their searches by adding class and field-level criteria in one or more "sets". Criteria within a set are AND'ed together. If multiple criteria sets are defined, the sets are OR'd.
Users should be able to search on any of the currently available criteria (e.g. Name/Email, Groups, Tags, Privacy Settings, custom fields, search fields defined by a component such as Quest/Student and CiviContribute, relationship search criteria).
Search Builder query criteria will be saved to / retrieved from the civicrm_mapping and mapping_fields tables - which will be extended to support "operator" and "criteria". This will be an extension of the process currently use for saving / retrieving Mapped Exports.
This JIRA task is to implement the User Interface (search form) for the Search Builder.
NOTE: Browse to the mockup at http://civicrm1.electricembers.net/~lobo/searchDesigner.html and refer to it (and the prototype notes on the bottom of that page) while reading the rest of this specification.
- Search Builder form will be accessed from a new localtask menu item in the civicrm/search path (civicrm/search/builder) - rendered as a 3rd navigation tab.
- On load, the form will be populated with 3 criteria "sets" (fieldsets), each with 5 criteria. Each criteria requires 4-5 actual form fields (class, field, operator, criteria value + conditionally a location type). This means initially loading 3 x 5 x 5 = 75 fields in the form - so make these limits easily modifiable if page load is problematic. However, we will hide all elements except the first criteria row in the first criteria (field)set. User clicks "another search field" to show/populate another criteria (AND'ed) within a set. User clicks "Also include contacts where..." to show/populate another critieria set.
- Each criteria (row) will be set up as a hierarchical select element. This functionality is exactly like the EXPORT selected fields form. Users will first select the "class" (e.g. Individual, Org, Household, Relationship, Activity History. Depending on enabled components, there will additional options for:
- Student (or other component-defined classes)
- Then user will select from the fields available for that "class" - including searchable custom fields as well as the special case "fields" of Group(s) and Tag(s).
- For any selected criteria, the next drop-down will provide OPERATOR options. For now, this will be static set of all valid MySQL operators. (ignore conditonal operator spec in the mockup).
- Location criteria will also offer a "location type" select - which will include defined loc types as well as "Primary" (to search on is_primary locations).
- The last field in the hierarchical select is the criteria (value) field. For now, this will be a simple text field. The user will be responsible for entering an appropriate value/format (e.g. a valid MySQL date, a number, a string). We will add single-quote's around string values if the SELECT field is char/varchar and the user doesn't enter them. For Groups and Tags, we will require use to enter group_id or tag_id.
NOTE: Searches on multiple tags or groups will be done by adding multiple criteria rows rather than the multiple checkbox select interface used in the existing search modes. This gives the user maximum flexibility in determining whether they want OR or AND filters on multiple tags/groups.
- Advanced search cases where we currently offer From / To value ranges (e.g. Activity Dates - From and To, or custom date and numeric fields) will also be handled as two separate criteria rows.
- In order to handle cases where the user needs additional criteria rows within a set (e.g. more than 10) - the "show" link after criteria row 5 reloads the form with 5 more criteria rows in that set. To handle cases where they need more than 3 criteria sets, the "show" link after the 3rd criteria set reloads the form with 1 additional criteria set (iteratively).
Saving Search Builder Queries as Smart Groups
"New Smart Group" and "Update Smart Group" tasks will create or update a DB representation of the search builder form values as follows:
- civicrm_group record with FK to civicrm_saved_search
- civicrm_saved_search record contains new mapping_id = FK to civicrm_mapping
- civicrm_mapping record = mapping_type="Search Builder"
- a civicrm_mapping_field record for each criteria row. New INT column in mapping_field = `grouping` is used to demarcate multiple criteria sets within a saved search-builder object.