CiviCRM Community Forums (archive)

I needed the capability to list households for individuals, so I created a custom search, and uploaded it to the wiki at http://wiki.civicrm.org/confluence/display/CRMDOC/List+Household+for+Individuals
I'm posting the description and code here as well. Any comments or corrections are appreciated.

Custom Search: List Household for Individuals
Search on the name, group, or tag of an Individual contact type, and list the results with the Name and Internal Contact ID for each Individual's Household. Does not return records for Individuals who are not members of a Household.

Usage: Add Households to a group or tag instead of (or in addition to) the Individuals that belong to the Household. You can export the search results in csv format, and then import them again, this time mapping the Household ID to Internal Contact ID during the import. You can then perform the usual operations on the search result: add to group, remove from group, tag, untag, etc.

Search on Name, Group and Tag, and display the results as:

    * Contact ID (Internal Contact ID for Individual)
    * Sort Name
    * Display Name
    * Household ID (Internal Contact ID for Household)
    * Household Name
    * Relationship Type

Relationship Type is not really necessary in the search results. It is hard-coded to 7 in the php file. It is included to verify that the records returned do indeed have the required relationship

This custom search is based on the existing 'EmployerListing.php' custom search. It has been tested on CiviCRM 2.2.2, running on Drupal 6.1.3.

How to use: Put the code below into a file called IndividualHousehold.php in CRM/Contact/Form/Search/Custom.
Then set it up in Administer CiviCRM -> Manage Custom Searches.

Custom Search Path: CRM_Contact_Form_Search_Custom_IndividualHousehold
Search Title: List Household for Individuals

<?php

/*
 +--------------------------------------------------------------------+
 | CiviCRM version 2.2                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2009                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007.                                       |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License along with this program; if not, contact CiviCRM LLC       |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
*/

  /**
   *
   * @package CRM
   * @copyright CiviCRM LLC (c) 2004-2009
   * $Id$
   *
   */

require_once 'CRM/Contact/Form/Search/Interface.php';

class CRM_Contact_Form_Search_Custom_IndividualHousehold
implements CRM_Contact_Form_Search_Interface {

    protected $_formValues;

    function __construct( &$formValues ) {
        $this->_formValues = $formValues;

        /**
         * Define the columns for search result rows
         */
        $this->_columns = array( ts('Contact Id')       => 'contact_id',
          ts('Sort Name') => 'sort_name',
                                 ts('Display Name')   => 'display_name' ,
                                 ts('Household Id')  => 'household_id' ,
                                 ts('Household Name') => 'household_name' ,
                                 ts('Relationship Type')=> 'relationship_type_id' );
    }

    function buildForm( &$form ) {

        /**
         * You can define a custom title for the search form
         */
        $this->setTitle('List the Household for Individual Contacts');

        // text for sort_name
        $form->add('text', 'sort_name', ts('Name'));

        // add select for groups
        $group =
            array('' => ts('- any group -')) +
            CRM_Core_PseudoConstant::group( );
        $form->addElement('select', 'group', ts('in'), $group);

        // add select for categories
        $tag =
            array('' => ts('- any tag -')) +
            CRM_Core_PseudoConstant::tag( );
        $form->addElement('select', 'tag', ts('Tagged'), $tag);

        /**
         * 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', 'group', 'tag' ) );
    }

    /*
     * Set search form field defaults here.
     */
    function setDefaultValues( ) {

    }

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

    /**
     * Construct the search query
     */
    function all( $offset = 0, $rowcount = 0, $sort = null,
                  $includeContactIDs = false ) {

        // SELECT clause must include contact_id as an alias for civicrm_contact.id
        $select = "
            DISTINCT cR.contact_id_a as contact_id,
            cInd.display_name as display_name,
            cInd.sort_name as sort_name,
            cR.contact_id_b as household_id,
            cHouse.household_name as household_name,
            cR.relationship_type_id as relationship_type_id
            ";

        $from  = $this->from( );

        $where = $this->where( $includeContactIDs );

        $having = $this->having( );
        if ( $having ) {
            $having = " HAVING $having ";
        }

        // Define GROUP BY here if needed.
        $grouping = "";

        $sql = "
            SELECT $select
            FROM   $from
            WHERE  $where
            $grouping
            $having
            ";
        // Define ORDER BY for query in $sort, with default value
        if ( ! empty( $sort ) ) {
            if ( is_string( $sort ) ) {
                $sql .= " ORDER BY $sort ";
            } else {
                $sql .= " ORDER BY " . trim( $sort->orderBy() );
            }
        } else {
            $sql .= "ORDER BY sort_name asc";
        }

        /* Uncomment the next 2 lines to see the exact query you're generating */
         // CRM_Core_Error::debug('sql',$sql);
         // exit();

        return $sql;
    }

    function from( ) {
        return "
            civicrm_relationship cR
            INNER JOIN civicrm_contact cInd ON ( cR.contact_id_a = cInd.id )
            INNER JOIN civicrm_group_contact cGC ON (cR.contact_id_a = cGC.contact_id)
            INNER JOIN civicrm_contact cHouse ON ( cR.contact_id_b = cHouse.id )
            ";
    }

    /*
     * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria 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 = 7";
//        $clauses[] = "cR.contact_id_a = cInd.id";
//        $clauses[] = "cR.contact_id_b = cHouse.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'";
        }

        $sel_group   = CRM_Utils_Array::value( 'group',
                                          $this->_formValues );
        if ( $sel_group != null ) {
            $clauses[] = "cGC.group_id = $sel_group";
        }

        $sel_tag   = CRM_Utils_Array::value( 'tag',
                                          $this->_formValues );
        if ( $sel_tag != null ) {
            $clauses[] = "cGC.tag_id = $sel_tag";
        }

        if ( $includeContactIDs ) {
            $contactIDs = array( );
            foreach ( $this->_formValues as $id => $value ) {
                if ( $value &&
                     substr( $id, 0, CRM_Core_Form::CB_PREFIX_LEN ) == CRM_Core_Form::CB_PREFIX ) {
                    $contactIDs[] = substr( $id, CRM_Core_Form::CB_PREFIX_LEN );
                }
            }

            if ( ! empty( $contactIDs ) ) {
                $contactIDs = implode( ', ', $contactIDs );
                $clauses[] = "contact.id IN ( $contactIDs )";
            }
        }

        return implode( ' AND ', $clauses );
    }

    function having( $includeContactIDs = false ) {
        $clauses = array( );
        return implode( ' AND ', $clauses );
    }

   /*
     * Functions below generally don't need to be modified
     */
    function count( ) {
        $sql = $this->all( );

        $dao = CRM_Core_DAO::executeQuery( $sql,
                                           CRM_Core_DAO::$_nullArray );
        return $dao->N;
    }

    function contactIDs( $offset = 0, $rowcount = 0, $sort = null) {
        return $this->all( $offset, $rowcount, $sort );
    }

    function &columns( ) {
        return $this->_columns;
    }

    function setTitle( $title ) {
        if ( $title ) {
            CRM_Utils_System::setTitle( $title );
        } else {
            CRM_Utils_System::setTitle(ts('Search'));
        }
    }

    function summary( ) {
        return null;
    }

}

?>


I'm using version 2.2
That's why I list that in the code.
I'm very interested in finding out how it works in 3.x, so thanks very much for the feedback.