4.5 Advanced search results show contacts where related contact is deleted

[ken]

(Detected in 4.5.0 and reproducible in 4.5.2 Drupal demo)

To reproduce ...

• Run an Advanced Search that looks for contacts in a relationship of type X
• Select contact Y from the results
• Delete each contact that is in relationship X with contact Y
• Visit contact Y to see that it has no relationships of type X
• Re-run the Advanced Search, and Y shows up in the results

In the database the civicrm_relationship rows of type X for contact Y still exist, so I suspect the query being run finds these but doesn't test that the related contact is deleted.

[ken]

This seems to have been identified in CRM-13456

But that bug was identified 4.3.5 and held over to 4.7. Is it just looking for someone to put up their hand, or is there a deeper issue?

[Donald Lobo]

yes, a lot of the bugs (like this one) are waiting on folks to contribute a PR that fixes the bugs. We've also been encouraging folks to submit a test to ensure that future changes do not break existing functionaility

lobo

[ken]

Lobo,

I can look to providing a PR, but I'm green when it comes to test cases. Could someone give me some pointers to a How To and to which test case I could copy for this?

Thanks,

Ken

[ken]

I've added PR https://github.com/civicrm/civicrm-core/pull/4367 and updated issue https://issues.civicrm.org/jira/browse/CRM-13456

Advanced Search does not filter relationships with deleted contacts.

Code: [Select]

diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php
index a1a70ae..2758ef7 100644
--- a/CRM/Contact/BAO/Query.php
+++ b/CRM/Contact/BAO/Query.php
@@ -2539,16 +2539,16 @@ class CRM_Contact_BAO_Query {
             }
             else {
               $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_i
-              $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id OR civicrm_relationship.cont
+              $from .= " $side JOIN civicrm_contact contact_b ON ((civicrm_relationship.contact_id_a = contact_b.id OR civicrm_relationship.con
             }
           }
           elseif (self::$_relType == 'b') {
             $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id )";
-            $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id )";
+            $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id AND contact_b.is_deleted != 1)
           }
           else {
             $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id )";
-            $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id )";
+            $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id AND contact_b.is_deleted != 1)
           }
           continue;
 

The change to line 2551 of /CRM/Contact/BAO/Query.php fixes this. The changes to lines 2542 and 2547 have been added for symmetry but haven't been tested. As this bit of code is probably used in a number of places, it would be good to have some further testing.

Note that http://dev.mysql.com/doc/refman/5.5/en/join.html says "The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set".

So this SQL will work but perhaps the best change would be to adjust the WHERE clause. It just seemed too hard to track that.