CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion »
  • APIs and Hooks (Moderator: Donald Lobo) »
  • WHERE clause for Contact Reference Fields
Pages: [1]

Author Topic: WHERE clause for Contact Reference Fields  (Read 572 times)

pbarmak

  • I post occasionally
  • **
  • Posts: 111
  • Karma: 3
  • CiviCRM version: 3.3.5
  • CMS version: Pressflow 6.19
  • MySQL version: 5.1
  • PHP version: 5.2.10
WHERE clause for Contact Reference Fields
November 26, 2014, 04:38:52 am
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

  • Ask me questions
  • ****
  • Posts: 638
  • Karma: 81
    • Palante Technology
  • CiviCRM version: 4.1 to the latest
  • CMS version: Drupal 6-7, Wordpress 4.0+
  • PHP version: PHP 5.3-5.5
Re: WHERE clause for Contact Reference Fields
November 26, 2014, 07:19:47 am
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');
Sign up to StackExchange and get free expert CiviCRM advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

Coleman Watts

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 2346
  • Karma: 183
  • CiviCRM version: The Bleeding Edge
  • CMS version: Various
Re: WHERE clause for Contact Reference Fields
November 26, 2014, 07:26:18 am
Quote
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 :D
Try asking your question on the new CiviCRM help site.

pbarmak

  • I post occasionally
  • **
  • Posts: 111
  • Karma: 3
  • CiviCRM version: 3.3.5
  • CMS version: Pressflow 6.19
  • MySQL version: 5.1
  • PHP version: 5.2.10
Re: WHERE clause for Contact Reference Fields
November 26, 2014, 10:34:22 am
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

  • Ask me questions
  • ****
  • Posts: 638
  • Karma: 81
    • Palante Technology
  • CiviCRM version: 4.1 to the latest
  • CMS version: Drupal 6-7, Wordpress 4.0+
  • PHP version: PHP 5.3-5.5
Re: WHERE clause for Contact Reference Fields
November 26, 2014, 12:27:41 pm
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.
Sign up to StackExchange and get free expert CiviCRM advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

Coleman Watts

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 2346
  • Karma: 183
  • CiviCRM version: The Bleeding Edge
  • CMS version: Various
Re: WHERE clause for Contact Reference Fields
November 26, 2014, 12:29:35 pm
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
Try asking your question on the new CiviCRM help site.

Coleman Watts

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 2346
  • Karma: 183
  • CiviCRM version: The Bleeding Edge
  • CMS version: Various
Re: WHERE clause for Contact Reference Fields
November 26, 2014, 03:36:38 pm
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
Try asking your question on the new CiviCRM help site.
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion »
  • APIs and Hooks (Moderator: Donald Lobo) »
  • WHERE clause for Contact Reference Fields

This forum was archived on 2017-11-26.