Details
-
Type: Bug
-
Status: Done/Fixed
-
Priority: Minor
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.2.2
-
Fix Version/s: 4.3.0
-
Component/s: Drupal Integration Modules
-
Labels:None
Description
At the moment, if I create a Drupal View of users, add a relationship based on a Civicrm Relationship, and choose to use a specific Civicrm Relationship in the views relationship form, I get malformed SQL queries. Here's an example that includes two views relationships:
SELECT users.name
FROM
LEFT JOIN {civicrm_uf_match} civicrm_uf_match ON users.uid = civicrm_uf_match.uf_id
LEFT JOIN {civicrm_relationship} civicrm_relationship ON civicrm_uf_match.contact_id = civicrm_relationship.contact_id_a
LEFT JOIN {civicrm_contact} civicrm_contact_civicrm_relationship ON civicrm_relationship.contact_id_b = civicrm_contact_civicrm_relationship.id AND civicrm_relationship.relationship_type_id = '1'
LEFT JOIN {civicrm_relationship} civicrm_relationship2 ON civicrm_uf_match.contact_id = civicrm_relationship2.contact_id_a
LEFT JOIN {civicrm_contact} civicrm_contact_civicrm_relationship_1 ON civicrm_relationship2.contact_id_b = civicrm_contact_civicrm_relationship_1.id AND civicrm_relationship2.relationship_type_id = '4'
What's malformed about it? The initial for both relationships does not filter on the relationship type. Instead, the relationship type is being filtered during the join to the civicrm_contact table. This causes many unwanted rows to be returned.
Ideally the query would look like this:
SELECT users.name
FROM {users}
users
LEFT JOIN
civicrm_uf_match ON users.uid = civicrm_uf_match.uf_id
LEFT JOIN
LEFT JOIN {civicrm_contact} civicrm_contact_civicrm_relationship ON civicrm_relationship.contact_id_b = civicrm_contact_civicrm_relationship.id
LEFT JOIN {civicrm_relationship}
civicrm_relationship2 ON civicrm_uf_match.contact_id = civicrm_relationship2.contact_id_a AND civicrm_relationship2.relationship_type_id = '4'
LEFT JOIN
civicrm_contact_civicrm_relationship_1 ON civicrm_relationship2.contact_id_b = civicrm_contact_civicrm_relationship_1.id
I've created a patch which achieves this (see attached) however I'm not sure it's the correct way to go about it and I hope someone with more views experience can have a good look at it.