Details
-
Type:
Bug
-
Status: Done/Fixed
-
Priority:
Trivial
-
Resolution: Fixed/Completed
-
Affects Version/s: 3.0, 3.1.1
-
Fix Version/s: 3.1.3
-
Component/s: CiviCRM Search
-
Labels:None
Description
When doing a Search Builder query on a Contribution date field the search returns no results. I confirmed this in my civi install (3.0.0) and the demo. If I do a Search Builder query on civicrm_contribution.receive_date > 20100201, the mysql general query log shows:
SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( ( LOWER(civicrm_contribution.receive_date) > 20100201 AND civicrm_contribution.is_test = 0 ) ) AND ( 1 )
The mysql LOWER() function in the WHERE clause is breaking the date comparison.
dlobo pointed me to CRM/Contribute/BAO/Query. I'm not sure why buildQuery() is handling the query not dateQueryBuilder(), but the following patch seems to do the trick and makes the search much faster. Overhead in the LOWER() function?
— /var/www/imba/sites/all/modules/civicrm/CRM/Contribute/BAO/Query.php 2010-02-11 23:22:33.000000000 +0000
+++ ./Query.php 2010-02-11 23:32:21.000000000 +0000
@@ -408,7 +408,7 @@
$dataType = "String";
}
- $wc = ( $op != 'LIKE' ) ? "LOWER($whereTable[where])" : "$whereTable[where]";
+ $wc = ( $op != 'LIKE' && $dataType != 'Date' ) ? "LOWER($whereTable[where])" : "$whereTable[where]";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause( $wc, $op, $value, $dataType) ;
$query->_qill[$grouping][] = "$whereTable[title] $op $quoteValue";
list( $tableName, $fieldName ) = explode( '.', $whereTable['where'], 2 );