[CRM-17571] Cannot search for Activity type and status in Search Builder (it translates values incorrectly) - CiviCRM Issue Tracker

Details

Type: Bug
Status: Done/Fixed
Priority: Major
Resolution: Fixed/Completed
Affects Version/s: 4.6.10, 4.6.11
Fix Version/s: 4.7
Component/s: CiviCRM Search
Labels:

Documentation Required?:
None
Funding Source:
Needs Funding

Description

We have just updated to version 4.6.10. and got the following issue (it was not present in 4.5.x). It can be reproduced in the demo site as well, like the following, for example:

Go to Search Builder and choose Activity, Activity Type = Pledge Acknowledgement .
It gives no results, although there are many activities with this type in the database.
You may choose any type and you get no results.
The behaviour is similar if searching for Activity Status.
However, Advanced Search works correctly.

I have figured out the problem behind but I don't know how to correct it yet: the Search Builder translates the values (labels) to id's as you can see on the empty search results page:

No matches found for:
Activity Type = 10

We logged the SQL queries behind a similar search on our deployment and recognized the wrong clause where the activity type id is treated as label string:

activity_type.label = '49'

It seems the generation of this sql part is what needs to be corrected.

I'm pasting the full SQL log entry here for information:

Nov 13 13:35:31  [info]
$Query = string(1082) "
INSERT INTO civicrm_prevnext_cache ( entity_table, entity_id1, entity_id2, cacheKey, data )
SELECT DISTINCT 'civicrm_contact', contact_a.id, contact_a.id, 'civicrm search 8e2a9553f9e1df9d5e9c2375b7cdf94e_8968', contact_a.display_name
  FROM civicrm_contact contact_a LEFT JOIN civicrm_activity_contact
                      ON ( civicrm_activity_contact.contact_id = contact_a.id )  LEFT JOIN civicrm_activity
                      ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
                      AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 ) LEFT JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type') LEFT JOIN civicrm_option_value activity_type ON (civicrm_activity.activity_type_id = activity_type.value
                               AND option_group_activity_type.id = activity_type.option_group_id )  WHERE  (  ( activity_type.label = '49' )  )  AND (contact_a.is_deleted = 0)   GROUP BY contact_a.id  ORDER BY contact_a.sort_name asc, contact_a.id  LIMIT 0, 500 "

Any comments are welcome - if someone can give me a tip where the problem might be in the code, I may try to propose a fix.