Introducing hook_civicrm_searchColumns: Modify search results to meet your needs

2011-08-31 10:36
Written by

We've been having a great gathering and an incredibly productive code sprint at wonderful Cawthorpe, UK. Thanx to Dave Moreton from Circle Interactive for organizing the logistics and making this event possible.

Over the past few weeks, at least 6 people have asked a very similar question on the forums. I want to extend the results from the Contact / Contribute / Member search form and display these fields instead of my least favorite fields. For example, users want to see the organization and job title of their contacts in the search form instead of country and post code. Or the total amount someone has contributed to the organization as part of the contribution search results. We had a few ways of doing this, but not very elegant.

I wanted to address and solve this during the code sprint. I looked at the code and initially came up with two hooks: one for the search headers and one for the search results. In a conversation with Kurund, we figured that one hook was a lot easier for downstream developers. I took a closer look and found an even better place to introduce the new hook: hook_civicrm_searchColumns. This will be part of 3.4.6

Basically, in the hook you modify the headers and values and inject / modify both the arrays as you see fit. The search templates currently are hard-wired to what it is going to display. So in some cases you might need to customize the search template also. You can work around this if you are keeping the same columns and overwriting the values in the array.Lo and behold, the very next day, my good friend (and author of the fabulous WebForm CiviCRM module made a similar request. Was kinda nice to let him know about this new hook. I suspect he'll test and QA it before its released. An example of how to add contribution total instead of the type field is below:

function civitest_civicrm_searchColumns( $objectName, &$headers,  &$values, &$selector ) {
    if ( $objectName == 'Contribute' ) {
        // rename type to total amount
        foreach ( $headers as $id => $header ) {
            if ( $header['name'] == 'Type' ) {
                $headers[$id]['name'] = 'Total';
                unset( $headers[$id]['sort'] );

        foreach ( $values as $id => $value ) {
           // the below SQL is not optimal from a performance perspective                                                           
           // you are doing 'n' queries, one for each contact_id                                                                               
           // a better query would be to group all the contact ids and do                                                                      
           // SELECT SUM(total_amount), contact_id FROM   civicrm_contribution WHERE contact_id                                                
           //  IN ( CONTACT_ID_LIST ) GROUP BY contact_id                                                                                      
           // and then loop around the result set                                                                                              
            $sql = "
SELECT SUM(total_amount)
FROM   civicrm_contribution
WHERE  contact_id = %1
            $values[$id]['total'] = CRM_Core_DAO::singleValueQuery( $sql,
                                                                    array( 1 => array( $value['contact_id'],
                                                                                       'Integer' ) ) );

            // this is cheating, but allows us NOT to modify the template
            // override the values that we are not using
            $values[$id]['contribution_type'] = $values[$id]['total'];

This hook also works on most of the Contact tabs (since all of them are hidden searches). If you have comments / questions let us know. Would be nice to get a few more eyes and tests on this before its part of the release

Filed under
Click thumbs up if you thought this blog post was useful (login to vote or to comment)


But beware the performance impact. In the above example, it means a new request for every row. If your user displays 100 rows, loading the page will be slow (and the server will grin).


If you can, try group fetching all the extra data. eg. looping through all the rows, get the ids needed, and do a big select where id in (big list of ids). One request only.