Custom Search Components

Building a Custom Search in CiviCRM

Use the Custom Search Framework to Create New Search Capabilities Developers and accidental techies with a bit of PHP and SQL knowledge can create new search forms to handle specific search and reporting needs which aren't covered by the built-in Advanced Search and Search Builder features. This document covers general steps for creating a new custom search component - using a simple example. Create a search which retrieves the Employer Name for individual contacts. We encourage folks who develop new custom searches - or extend and improve existing custom searches - to post the files as child pages here. Please include a brief description of the purpose / use case(s) for the search, and any limitations / issues with the implementation. Searches that are of general interest may be added to future distributions.

Design your search.

• What search criteria are needed (e.g. what fields do you need on the search form)?
• What should the result set look like (rows, columns, aggregate / calculated values)?

• • NOTE: Only the defined result set columns will be included if you export search results to a CSV file.
• Write and test a SQL query that will return the result set you want. Use hard-coded search criteria in your test query.

• • Refer to the CiviCRM Schema Entity-Relationship Diagrams for a good overview of the tables and relationships in the database.

    EXAMPLE: Retrieve Employer for individual contacts Search criteria: Last Name, Home State Result rows will contain: Individual's "sort name" (last, first), Current Employer Name Test query: SELECT distinct(cInd.id) as contact_id, cInd.sort_name as sort_name, indSP.name as indState, cEmp.sort_name as employer, empSP.name as empState \\ FROM civicrm_relationship cR, civicrm_contact cInd LEFT JOIN civicrm_address indAddress ON ( indAddress.contact_id = cInd.id AND indAddress.is_primary = 1 ) LEFT JOIN civicrm_state_province indSP ON indSP.id = indAddress.state_province_id, \\ civicrm_contact cEmp LEFT JOIN civicrm_address empAddress ON ( empAddress.contact_id = cEmp.id AND empAddress.is_primary = 1 ) LEFT JOIN civicrm_state_province empSP ON empSP.id = empAddress.state_province_id \\ WHERE cInd.contact_type = 'Individual' AND cR.relationship_type_id = 4 AND cR.contact_id_a = cInd.id AND cR.contact_id_b = cEmp.id AND cR.is_active = 1 AND /* Test "Last Name" and "Individual State" search criteria with hard-coded values. */ cInd.sort_name LIKE 'Zo%' AND indSP.id = 1000 \\ ORDER BY sort_name asc;

    Copy an existing custom search file under a new name, so you can use it as a "template".

    If you are creating your own custom search file, you should create a path for the php and tpl files outside of the CiviCRM codebase. To do this:

1. Within CiviCRM, go to: Administer > Configure > Global Settings > Directories
2. Fill in the Custom PHP Path Directory. This needs to be an absolute path to your PHP directory, such as /home2/jsmith/public_html/civicrm_custom_code/
3. Create a copy of an existing custom search file, such as the ContributionAggregate.php file. You will find this file at:

<joomla root>/administrator/components/com_civicrm/civicrm/CRM/Contact/Form/Search/Custom
or
<drupal root>/sites/all/modules/civicrm/CRM/Contact/Form/Search/Custom

1. Place the ContributionAggregate.php file in the directory: /home2/jsmith/public_html/civicrm_custom_code/CRM/Contact/Form/Search/Custom
2. Rename the copied ContributionAggregate.php file to EmployerListing.php and update the class definition in the copied file:

class CRM_Contact_Form_Search_Custom_employerListing
  implements CRM_Contact_Form_Search_Interface {

Register your custom search component

In 2.1 and later there are admin screens to do the following. Navigate to civicrm/admin/options/custom_search?group=custom_search&reset=1 or ?q=civicrm/admin/options/custom_search&group=custom_search&reset=1 (if the first link doesn't take you to Custom Search Options, but to Option Groups instead)

* You will need to insert a record in civicrm_option_value to register your new search. The registration record includes an integer "value" - which is the invoke ID for the search component, the class path (defined in previous step), and the file name.

• First, browse the existing custom search rows to determine the next available integer "value".

SELECT * FROM `civicrm_option_value`
WHERE option_group_id = (
   SELECT id FROM civicrm_option_group
 WHERE name = 'custom_search'
 )

In my database, the highest "value" row was 5. So I'll use 6 as the `value` for our new search. Remember this value as we will use it in the URL to view the search form later. The class path is the `label`. The file path and name is the `name`. So, for our Employee Listing example - the insert query is:

SELECT @og_id := id FROM civicrm_option_group WHERE domain_id = 1 AND name = 'custom_search';
INSERT INTO civicrm_option_value
 (option_group_id, value, label, name)
VALUES
 (@og_id, 6, 'CRM_Contact_Form_Search_Custom_EmployerListing', 'CRM/Contact/Form/Search/Custom/EmployerListing.php');

Modify functions in the new custom search file to meet your design.

Define result set columns.

• Using an array in the __construct() function. Array item names are the column headers, values are the column names you've specified in your query SELECT clause. These are also the columns you'll get when you export search results.

/**
 * Define the columns for search result rows
 */
$this->_columns = array( ts('Contact Id')       => 'contact_id',
                         ts('Individual Name')  => 'sort_name' ,
                         ts('Individual State') => 'indState' ,
                         ts('Employer')         => 'employer' ,
                         ts('Employer State')   => 'empState' );

Create search form fields.

• Modify buildForm() to implement your search criteria (user input) fields. Check the PHP files for other forms in the CiviCRM codebase for additional examples (radio buttons, checkboxes, etc.) Refer to PEAR QuickForm documentation for more details.

/**
 * Define the search form fields here
 */
$form->add( 'text',
           'sort_name',
           ts( 'Individual\'s Name (last, first)' ) );
\\
$stateProvince = array('' => ts('- any state/province -')) + CRM_Core_PseudoConstant::stateProvince( );
$form->addElement('select', 'state_province_id', ts('Individual\'s State'), $stateProvince);
\\
/**
 * If you are using the sample template, this array tells the template fields to render
 * for the search form.
 */
$form->assign( 'elements', array( 'sort_name', 'state_province_id') );

* If you want defaults for any form values - create a setDefaultValues() function and define them there.

// Setting default search state to California
function setDefaultValues( ) {
    return array( 'state_province_id' => 1004, );
}

Define SELECT clause and DEFAULT SORT in the all() function.

• Grab the SELECT from our test query above...

// SELECT clause must include contact_id as an alias for civicrm_contact.id
$select  = "
distinct(cInd.id) as contact_id,
cInd.sort_name as sort_name,
cEmp.sort_name as employer,
state_province.name as empState
";

Define a default sort - we'll use sort_name asc.

if ( ! empty( $sort ) ) {
...
} else {
    $sql .= "ORDER BY sort_name asc";
}
...

If you want to modify the value returned in a cell...

• Define an alterRow() function:

// This trivial example appends ' (altered )' to the sort name in each row
function alterRow( &$row ) {
    $row['sort_name'] .= ' ( altered )';
}

Define FROM clause.

• Grab it from our test query and put it in the from() function return string.

function from( ) {
    return "
    civicrm_relationship cR,
    civicrm_contact cInd
    LEFT JOIN civicrm_address indAddress ON ( indAddress.contact_id = cInd.id AND
    indAddress.is_primary       = 1 )
    LEFT JOIN civicrm_state_province indSP ON indSP.id = indAddress.state_province_id,
    civicrm_contact cEmp
    LEFT JOIN civicrm_address empAddress ON ( empAddress.contact_id = cEmp.id AND
    empAddress.is_primary       = 1 )
    LEFT JOIN civicrm_state_province empSP ON empSP.id = empAddress.state_province_id
    ";
}

Define WHERE clause.

• This is an array built from any required JOINS and filters, and then adding conditional filters based on search form field values.

function where( $includeContactIDs = false ) {
    $clauses = array( );
\\
    // These are required filters for our query.
    $clauses[] = "cInd.contact_type = 'Individual'";
    $clauses[] = "cR.relationship_type_id = 4";
    $clauses[] = "cR.contact_id_a = cInd.id";
    $clauses[] = "cR.contact_id_b = cEmp.id";
    $clauses[] = "cR.is_active = 1";
\\
    // These are conditional filters based on user input
    $name   = CRM_Utils_Array::value( 'sort_name',
                                     $this->_formValues );
    if ( $name != null ) {
        if ( strpos( $name, '%' ) === false ) {
            $name = "%{$name}%";
        }
        $clauses[] = "cInd.sort_name LIKE '$name'";
    }
\\
    $state = CRM_Utils_Array::value( 'state_province_id',
                                    $this->_formValues );
    if ( $state ) {
        $clauses[] = "indSP.id = $state";
    }
 ...

Define HAVING clause.

• If you're query includes GROUPING, and you are filtering based on aggregate values, you'll need to define a having() function. Refer to ContributionAggregate.php for an example (we don't need this for our current exercise).

Decide on page layout.

You can stick with the standard sample template to layout your search form and results (CRM/Contact/Form/Search/Custom/Sample.tpl), or create your own. The sample template simply loops through the list of fields you've defined in the 'elements' array above, and arranges them in a table - one field per row. If you want to lay out the form differently, and/or add styling, custom javascript etc. - create and modify a copy of that file. Then update the templateFile() function in your .php file to point to your new template.

/**
  * Define the smarty template used to layout the search form and results listings.
  */
function templateFile( ) {
 return 'CRM/Contact/Form/Search/Custom/Sample.tpl';
}

Test your search.

• To load the search form, navigate to the following URL. In our example, the custom search record "value" we inserted was 6, so we use csid=6.

Drupal:
http://<your site>-/civicrm/contact/search/custom?csid=6&reset=1
\\
Joomla:
http://<your site>-/administrator/index2.php?option=com_civicrm&task=civicrm/contact/search/custom&csid=6&reset=1

If you're not getting expected results, you can print out the SQL query being issued from the search form by adding these lines just before the return statement at the end of the all() function:

CRM_Core_Error::debug('sql',$sql);
   exit();

If your search returns too many rows check that function all has a LIMIT statement appended to the sql:

if ( $rowcount > 0 && $offset >= 0 ) {
    $sql .= " LIMIT $offset, $rowcount ";
}