WHERE clause for Contact Reference Fields

[pbarmak]

Hey all,
I need a pointer to where in the code the WHERE clause is generated for contact reference custom fields.  In 4.2.19, we see some strange WHERE clauses being generated.

For example, we type "living water" into the contact reference field. The database where clause generated is the following, which I believe is incorrect:

Code: [Select]

WHERE  ( contact_a.is_deleted = 0 AND  (  (  ( contact_a.sort_name LIKE '%living%' ) OR  ( civicrm_email.email LIKE '%living%' )  )  OR  (  ( contact_a.sort_name LIKE '%water%' ) OR  ( civicrm_email.email LIKE '%water%' )  )  )  )
This seems to look for any contact with "living" in the name/email OR any contact with "water" in the name/email.  It does NOT look for any contact with "living water" (full string with space) in the name.  That seems incorrect to me.  It should only return results with both words separated by space.  If we keep typing a third word, it gets worse, looking for contacts with any of the three words.

So, I'd like to see where this WHERE clause is generated to see if we can modify/fix it.  I see that the contact reference field calls CRM/Contact/Page/AJAX and that calls the api query.  But I can't see where to go from there.  I looked in CRM/Contact/BAO as well, but not seeing it.

Any help would be appreciated.

Also, does anyone know if this is resolved in 4.4?  I saw on the demo page (4.5) that it seems better (seems to be a combination of both using the full string plus finding just the single words), but we're a bit away from upgrading to 4.5.

[JonGold]

This is similar to CRM-15442 - but in fact the fix there is only for EntityRef fields, and Contact Reference fields haven't (yet) been converted to EntityRef fields even in Civi 4.5.

I patched one of my clients to fix contact reference fields in Civi 4.4 based on the fix in CRM-15442.  Here's my diff, you can probably apply something very similar:

Code: [Select]

diff --git a/sites/all/modules/contrib/civicrm/CRM/Contact/Page/AJAX.php b/sites
index 90fc183..48fccc3 100644
--- a/sites/all/modules/contrib/civicrm/CRM/Contact/Page/AJAX.php
+++ b/sites/all/modules/contrib/civicrm/CRM/Contact/Page/AJAX.php
@@ -96,6 +96,7 @@ class CRM_Contact_Page_AJAX {
 
   static function contactReference() {
     $name = CRM_Utils_Array::value('s', $_GET);
+    $name = str_replace(' ', '%', $name);
     $name = CRM_Utils_Type::escape($name, 'String');
     $cfID = CRM_Utils_Type::escape($_GET['id'], 'Positive');

[Coleman Watts]

in fact the fix there is only for EntityRef fields, and Contact Reference fields haven't (yet) been converted to EntityRef fields even in Civi 4.5.

Ha, what a mouthful! Congrats Jon for being one of the few people on the planet whose brain-cells are crammed with this bit of Civi trivia

[pbarmak]

Thanks so much, Jon, that's a great work-around for now.

Is there a separate issue being tracked to have contact reference fields be converted to entityref fields that I can follow?

[JonGold]

If Coleman is still following this thread, he's the guy to ask, since he wrote the EntityRef stuff.  I don't know of a ticket to track that - it's just that when I tried to troubleshoot this exact issue, there was a "FIXME" in the code comments.

[Coleman Watts]

Not really. When I was writing the EntityRef code I looked into what it would take and it looks like a large undertaking. There's a lot of legacy behavior in contactRef fields and inconsistencies between the two, esp in the area of permissions, which would need to be addressed. It's just not very high on the list of thousands of priorities for the project.
I've added a note about this to https://issues.civicrm.org/jira/browse/CRM-15442

[Coleman Watts]

Ideally I'd like to fix this at the query level for 4.6. I've filed https://issues.civicrm.org/jira/browse/CRM-15653