Search for people who have NOT registered for an event using a custom search or data processor

Question

I need to search for all users who have not registered for an event. Five years ago, I asked the question, and the answer was to create a group of users who did register and then do an include/exclude search.

The thing is that I need to do this on a regular basis, for many events. Creating a group for every event became too cumbersome.

I am trying to figure out how I can do this search, perhaps using the data processor extension. Where I am stuck is figuring out how to express my intention: "all users who have not registered".

• I tried using a search on Participants. But if someone has not registered, they are not a participant. So using a search like "all participants for event X where status is not Registered" only shows me people who did register but whose status is now Attended or Canceled for example. It does not show me people who have not registered.

• The data processor extension allows you to join data, but I don't see how it lets you do the opposite of a join. Essentially to say "select all users" plus "exclude (reverse of join) any person who is a Participant for this event".

I believe this is where I am thinking about the problem wrong. SQL doesn't have such an exclusion call, instead you are supposed to re-think the question so that it becomes a query where join is possible.

So can anyone help me re-think this so that using "join" function of data processor is possible?

Answer 1

SELECT cc.id, cp.id
FROM civicrm_contact cc 
LEFT JOIN civicrm_participant cp ON cc.id = cp.contact_id AND cp.event_id = 1 WHERE cp.id IS NULL;

You can confirm that this works by running this straightforward query to see the contacts who ARE registered for an event:

SELECT cc.id, cp.id 
FROM civicrm_contact cc 
LEFT JOIN civicrm_participant cp ON cc.id = cp.contact_id 
WHERE cp.event_id = 1;

Then confirm that this query is functionally the same:

SELECT cc.id, cp.id
FROM civicrm_contact cc 
LEFT JOIN civicrm_participant cp ON cc.id = cp.contact_id AND cp.event_id = 1 WHERE cp.id IS NOT NULL;

The first query is the same as the third, except uses IS NULL instead of IS NOT NULL.

This search should be possible with the forthcoming Search Kit. I tried a pre-release version but there was an error joining contacts to participants. However, I was able to create the equivalent with contributions - "All contacts without a member dues payment". The screenshot below shows how you'll be able to do this with Search Kit very soon:

Answer 2

Not quite what you asked, but Search Kit is based on APIv4 and you can also do it directly with APIv4:

$contacts = \Civi\Api4\Contact::get()
  ->addSelect('id')
  ->setJoin([
    ['Participant AS participant', FALSE, NULL,
    ['participant.contact_id', '=', 'id'], ['participant.event_id', '=', '1']]
  ])
  ->setGroupBy(['id'])
  ->addWhere('participant.id', 'IS NULL')
  ->execute();

(see also https://civicrm.stackexchange.com/a/37837/225)