|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.
- NOTE: It is important to include the contact_id in your select statement. Even if you do not intend to display it to users, it is used in the edit and view links that are created.
- Write and test a SQL query that will return the result set you want. Use hard-coded search criteria in your test query.
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:
- Within CiviCRM, go to: Administer > System Settings > Directories
- 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/
- Create a copy of an existing custom search file, such as the ContributionAggregate.php file. You will find this file at:
- Place the ContributionAggregate.php file in the directory: /home2/jsmith/public_html/civicrm_custom_code/CRM/Contact/Form/Search/Custom
- Rename the copied ContributionAggregate.php file to EmployerListing.php and update the class definition in the copied file:
Register your custom search component
- Navigate to the Custom Search Option Groups screen, you can do this one of two ways:
- In 2.1 or later, go to Administer>System Settings>Option Groups, scroll down to Custom Search and click Options, or
- Go to http://your_url/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, try navigating to: http://your_url/?q=civicrm/admin/options/custom_search&group=custom_search&reset=1)
- 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".
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:
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.
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.
* If you want defaults for any form values - create a setDefaultValues() function and define them there.
Define SELECT clause and DEFAULT SORT in the all() function.
- Grab the SELECT from our test query above...
Define a default sort - we'll use sort_name asc.
If you want to modify the value returned in a cell...
- Define an alterRow() function:
Define FROM clause.
- Grab it from our test query and put it in the from() function return string.
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.
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.
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.
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:
If your search returns too many rows check that function all has a LIMIT statement appended to the sql: