Using API4 to get contact based on email match

Question

The API Explorer is excellent for testing proper queries, but I can not figure out how to get a contact based on matching email address.

For example, I can get a contact based on a partial match of last name like this:

// get any contacts that contain 'smith' in the last_name field
$contacts = civicrm_api4('Contact', 'get', [
  'where' => [
    ['last_name', 'LIKE', 'smith'],
  ],
]);

But since email is in a different table than the contact information, I think I need to use some kind of JOIN command, but I am having trouble figuring out how to accomplish this.

So I need to any contacts where last_name LIKE 'smith' AND email='[email protected]'

Answer 1

Unless you have a complex join, it's simply a matter of referencing contact_id (the literal string prefix contact_id., not a numeric contact id) in the select() or where() to create the join, e.g.

$x = \Civi\Api4\Email::get()
->addWhere('email', '=', '[email protected]')
->addWhere('contact_id.last_name', '=', 'Smith')
->addSelect('contact_id.display_name')
->execute()->first();
print_r($x);

If it's more complex see https://docs.civicrm.org/dev/en/latest/api/v4/explicit-joins/

Answer 2

So @Demerit's answer above was exactly what I needed to understand how to connect the two tables together (email table and contact info table). I am posting my answer for other noobs that are trying to understand how this works.

In my question I was trying to GET CONTACT info first with the matching email address, however this appears to be far more complex than Demerit's solution which was to GET EMAIL info first with matching contact info... I am sure there is a way to do the complex call, but the exact query that I was looking for in my question above is this (get the email and then its matching contact info):

$emails = civicrm_api4('Email', 'get', [
  'select' => [
    '*', 
    'contact_id.*',
  ],
  'where' => [
    ['email', '=', '[email protected]'], 
    ['contact_id.last_name', 'LIKE', '%smith%'],
    ['contact_id.is_deleted', '=', FALSE], 
  ],
]);

The SELECT statement * pulls all the fields from the email table, and then the SELECT statement contact_id.* pulls all the fields from the related contact table.

The WHERE clause is looking for an email exact match in the email table, with a last_name that contains 'smith' from the related contact table.

Note for 'contains' to work properly, you need to use the LIKE operator, and then wrap your search term in between percent signs '%smith%'. Percent sign is like a wildcard in MySQL. So BEGINS WITH would be LIKE 'smith%' and ENDS WITH would be LIKE '%smith'.

Also note that when you delete a contact, it is not actually deleted from the database (a flag is set that says is_deleted=true), so if you want to ignore deleted contacts you can include a check for is_deleted being false.