Activity contact is null challenge
I just tried & failed to do the following search
I was attempting to document how to do a search for 'new dads who have not yet had a home visit'
The search looks like this
However it's tough to make this query work because the join winds up looking like
LEFT JOIN `civicrm_activity_contact` `Contact_ActivityContact_Activity_01_via_activitycontact` ON `Contact_ActivityContact_Activity_01_via_activitycontact`.`contact_id` = `a`.`id` AND `Contact_ActivityContact_Activity_01_via_activitycontact`.`record_type_id` = "3"
LEFT JOIN `civicrm_activity` `Contact_ActivityContact_Activity_01` ON AND `Contact_ActivityContact_Activity_01_via_activitycontact`.`activity_id` = `Contact_ActivityContact_Activity_01`.`id` AND `Contact_ActivityContact_Activity_01`.`activity_type_id` = "1"
WHERE `Contact_ActivityContact_Activity_01`.`id` IS NULL
But in fact you need to do
SELECT * FROM civicrm_contact a
LEFT JOIN `civicrm_relationship_cache` `Contact_RelationshipCache_Contact_01_via_relationshipcache`
ON `Contact_RelationshipCache_Contact_01_via_relationshipcache`.`far_contact_id` = `a`.`id`
AND `Contact_RelationshipCache_Contact_01_via_relationshipcache`.`near_relation` = "Child of"
INNER JOIN `civicrm_contact` `Contact_RelationshipCache_Contact_01`
ON `Contact_RelationshipCache_Contact_01_via_relationshipcache`.`near_contact_id` = `Contact_RelationshipCache_Contact_01`.`id`
LEFT JOIN `civicrm_activity_contact` `Contact_ActivityContact_Activity_01_via_activitycontact`
ON `Contact_ActivityContact_Activity_01_via_activitycontact`.`contact_id` = `a`.`id`
AND `Contact_ActivityContact_Activity_01_via_activitycontact`.`record_type_id` = "3"
AND activity_id IN (SELECT id FROM civicrm_activity WHERE activity_type_id = 1)
LEFT JOIN `civicrm_activity` `Contact_ActivityContact_Activity_01`
ON `Contact_ActivityContact_Activity_01_via_activitycontact`.`activity_id` = `Contact_ActivityContact_Activity_01`.`id`
WHERE `Contact_ActivityContact_Activity_01`.`id` IS NULL
AND (`Contact_RelationshipCache_Contact_01`.`birth_date` BETWEEN "20210301" AND "20210331")
AND (`a`.`is_deleted` = "0")
This is a particular challenge with bridging entities....