Contact.get query wrongly excludes contacts when return includes custom fields
On latest master (but not on 5.13 or 5.14), Contact.get
does not return contacts that do not have any activities if the API call asks for custom fields to be returned. Sample requests:
cv api Contact.get sequential=1 id=203
{
"is_error": 0,
"version": 3,
"count": 1,
"id": 203,
"values": [
{
"contact_id": "203",
"contact_type": "Individual",
...
}
]
}
cv api Contact.get sequential=1 return="custom_1" id=203
{
"is_error": 0,
"version": 3,
"count": 0,
"values": []
}
The API call generates this query:
SELECT contact_a.id as contact_id, civicrm_value_volunteer_com_6.id as civicrm_value_volunteer_com_6_id, civicrm_value_volunteer_com_6.volunteer_project_id_12 as custom_12, civicrm_value_volunteer_inf_5.id as civicrm_value_volunteer_inf_5_id, civicrm_value_volunteer_inf_5.camera_skill_level_11 as custom_11, civicrm_value_constituent_information_1.id as civicrm_value_constituent_information_1_id, civicrm_value_constituent_information_1.most_important_issue_1 as custom_1, civicrm_value_constituent_information_1.marital_status_2 as custom_2, civicrm_value_constituent_information_1.marriage_date_3 as custom_3, civicrm_value_donor_information_3.id as civicrm_value_donor_information_3_id, civicrm_value_donor_information_3.how_long_have_you_been_a_donor_6 as custom_6, civicrm_value_donor_information_3.known_areas_of_interest_5 as custom_5, civicrm_value_food_preference_2.id as civicrm_value_food_preference_2_id, civicrm_value_food_preference_2.soup_selection_4 as custom_4, civicrm_value_civivolunteer_4.id as civicrm_value_civivolunteer_4_id, civicrm_value_civivolunteer_4.volunteer_need_id_7 as custom_7, civicrm_value_civivolunteer_4.time_scheduled_in_minutes_9 as custom_9, civicrm_value_civivolunteer_4.volunteer_role_id_8 as custom_8, civicrm_value_civivolunteer_4.time_completed_in_minutes_10 as custom_10 FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
LEFT JOIN civicrm_participant ON civicrm_participant.contact_id = contact_a.id
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 )
INNER JOIN civicrm_contact ON ( civicrm_activity_contact.contact_id = civicrm_contact.id and civicrm_contact.is_deleted != 1 )
LEFT JOIN civicrm_value_civivolunteer_4 ON civicrm_value_civivolunteer_4.entity_id = `civicrm_activity`.id
LEFT JOIN civicrm_value_constituent_information_1 ON civicrm_value_constituent_information_1.entity_id = `contact_a`.id
LEFT JOIN civicrm_value_donor_information_3 ON civicrm_value_donor_information_3.entity_id = `civicrm_contribution`.id
LEFT JOIN civicrm_value_food_preference_2 ON civicrm_value_food_preference_2.entity_id = `civicrm_participant`.id
LEFT JOIN civicrm_value_volunteer_com_6 ON civicrm_value_volunteer_com_6.entity_id = `civicrm_activity`.id
LEFT JOIN civicrm_value_volunteer_inf_5 ON civicrm_value_volunteer_inf_5.entity_id = `contact_a`.id WHERE ( contact_a.id = '203' ) LIMIT 0, 25
It contains an INNER JOIN
based on civicrm_activity_contact
, so any contacts without rows in civicrm_activity_contact
would not be included.
In https://github.com/civicrm/civicrm-core/pull/14394 @eileen managed to get it to INNER JOIN
on civicrm_case
as well, so there might be different variants of this issue.