4

How can I find contacts using the APIv4 that don't have any relationships of a specific type?

I want the first n contacts ordered by a date field meeting various conditions excluding those with a relationship of type X.

There are various ways it could be done with multiple calls or php looping but it would be nice to be able to do it in a single call.

In SQL one option using a subquery is:

SELECT * FROM Contacts
WHERE <various things>
AND contact_id NOT IN (SELECT contact_id_a FROM Relationships WHERE <conditions>)
ORDER BY <something>
LIMIT <n>

Can APIv4 produce something like that?

1 Answer 1

4

Yes you can! I fiddled with the APIv4 Explorer a bit and came up with this:

APIv4 Explorer

The trick is to join onto RelationshipCache with the relationship type(s) you are wanting to exclude (note that the double quotes around '"Parent of"' are needed because you are passing a string literal not the name of a field or other SQL expression). And then in the WHERE clause specify that you want the relationship_cache.id to be NULL.

The GroupBy is necessary because your join can potentially produce duplicate results if the contact has more than one relationship, although technically that's impossible with this query so, meh, you could try leaving it off.

1
  • This is a good pattern for NOT IN (subquery).
    – Joe Murray
    Oct 7, 2020 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.