CiviCRM Community Forums (archive)

More precisely:

ANY 'Search Builder' search employing the 'State' field now fails with a fatal error as above, whereas an 'Advanced Search' using the same value on the State field succeeds.

Ran into this as well, the following changed corrected our issue.  The strings were not being quoted.


Index: modules/civicrm/CRM/Contact/BAO/Query.php
===================================================================
--- modules/civicrm/CRM/Contact/BAO/Query.php   (revision 47)
+++ modules/civicrm/CRM/Contact/BAO/Query.php   (working copy)
@@ -2931,10 +2931,9 @@
         }
 
         $stateClause =
-            'civicrm_state_province.id IN (' .
-            implode( ',', $value ) .
-            ')';
-
+            'civicrm_state_province.id IN ("' .
+            implode( '","', $value ) .
+            '")';
         $this->_tables['civicrm_state_province'] = 1;
         $this->_whereTables['civicrm_state_province'] = 1;

While that fix eliminated the fatal error, the queries didn't actually produce the expected results.  I reverted part of the stateProvince function to fix this.  I'm not sure what those changes were attempting to fix, but my Search Builder queries work again.

We're using Drupal as the CMS, but I don't think that makes a difference with this error.

  /**
     * where / qill clause for state/province AND country (if present)
     *
     * @return void
     * @access public
     */
    function stateProvince( &$values, $status = null )
    {
        list( $name, $op, $value, $grouping, $wildcard ) = $values;
       
        if (! is_array( $value ) ) {
            // force the state to be an array
            $value = array( $value );
        }
       
        $stateClause =
        'civicrm_state_province.id IN ("' .
         implode( '","', $value ) .
         '")';

        $this->_tables['civicrm_state_province'] = 1;
        $this->_whereTables['civicrm_state_province'] = 1;
           
        $stateProvince =& CRM_Core_PseudoConstant::stateProvince();
        $names = array( );
        foreach ( $value as $id ) {
            $names[] = $stateProvince[$id];
        }
           

        $countryValues = $this->getWhereValues( 'country', $grouping );
        list( $countryClause, $countryQill ) = $this->country( $countryValues, true );

        if ( $countryClause ) {
            $clause = ( $stateClause AND $countryClause );
        } else {
            $clause = $stateClause;
        }
       
        if (!$status) {
        $this->_qill[$grouping][] = ts('State/Province') . ' - ' . implode( ' ' . ts('or') . ' ', $names );
        } else {
        return implode( ' ' . ts('or') . ' ', $names );
        }
           
        return $this->restWhere( $values );
       
    }
I'm having a really had time following the how these issues are tracked.  There is an issue in Jira with a status of closed that points back to this thread...

http://issues.civicrm.org/jira/browse/CRM-8255

But this issue still exists in 3.44 and it doesn't look like @jerdavis's changes were implemented.

https://fisheye2.atlassian.com/browse/CiviCRM/branches/v3.4/CRM/Contact/BAO/Query.php?r=35033#to2924

Does the existing issues status need to be changed or do we need to open a new issue?

We're still having this issue in the latest version. For some reason the values being passed in for the IN query are not being quoted, and as a result are blowing up the query.

The generation of the clause is happening here:


 function stateProvince( &$values, $status = null )
    {
        list( $name, $op, $value, $grouping, $wildcard ) = $values;

        if (! is_array( $value ) ) {
            // force the state to be an array
            $value = array( $value );
        }

        $stateClause =
            'civicrm_state_province.id IN (' .
            implode( ',', $value ) .
            ')';