Answers.Body: <p>Unless you have a complex join, it's simply a matter of referencing contact_id (the literal string prefix <code>contact_id.</code>, not a numeric contact id) in the select() or where() to create the join, e.g.</p>
  <pre><code>$x = \Civi\Api4\Email::get()
  -&gt;addWhere('email', '=', 'me@example.com')
  -&gt;addWhere('contact_id.last_name', '=', 'Smith')
  -&gt;addSelect('contact_id.display_name')
  -&gt;execute()-&gt;first();
  print_r($x);
  </code></pre>
  <p>If it's more complex see <a href="https://docs.civicrm.org/dev/en/latest/api/v4/explicit-joins/" rel="nofollow noreferrer">https://docs.civicrm.org/dev/en/latest/api/v4/explicit-joins/</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-04-28T03:56:09.247Z
Answers.LastActivityDate: 2022-04-28T03:56:09.247Z
Answers.UserDisplayName: Demerit
Answers.Body: <p>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.</p>
  <p>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):</p>
  <pre><code>$emails = civicrm_api4('Email', 'get', [
    'select' =&gt; [
      '*', 
      'contact_id.*',
    ],
    'where' =&gt; [
      ['email', '=', 'aaa@bbb.com'], 
      ['contact_id.last_name', 'LIKE', '%smith%'],
      ['contact_id.is_deleted', '=', FALSE], 
    ],
  ]);
  </code></pre>
  <p>The SELECT statement <code>*</code> pulls all the fields from the email table, and then the SELECT statement <code>contact_id.*</code> pulls all the fields from the related contact table.</p>
  <p>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.</p>
  <p>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'.</p>
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-04-28T14:02:00.840Z
Answers.Text: Nice breakdown.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-04-28T13:55:24.720Z
Answers.LastActivityDate: 2022-04-28T14:03:00.453Z
Answers.LastEditDate: 2022-04-28T14:03:00.453Z
Answers.UserDisplayName: jsherk
Body: <p>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.</p>
  <p>For example, I can get a contact based on a partial match of last name like this:</p>
  <pre><code>// get any contacts that contain 'smith' in the last_name field
  $contacts = civicrm_api4('Contact', 'get', [
    'where' =&gt; [
      ['last_name', 'LIKE', 'smith'],
    ],
  ]);
  </code></pre>
  <p>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.</p>
  <p>So I need to any contacts where <code>last_name LIKE 'smith' AND email='aaa@bbb.com'</code></p>
  
Tags: <wordpress><contacts><api><apiv4>
Title: Using API4 to get contact based on email match
ThreadText: # Using API4 to get contact based on email match
  
  (Asked by: username_0 on 2022-04-28)
  
  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='aaa@bbb.com'
  
  **Tags:** wordpress, contacts, api, apiv4
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2022-04-28. Score: 1 | Accepted answer
  
  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', '=', 'me@example.com')
  ->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 by username_0 on 2022-04-28. Score: 2
  
  So @username_1'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 username_1'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', '=', 'aaa@bbb.com'], 
      ['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.
  
  > **Comments:**
  > **username_1**: Nice breakdown.  
  
  ---
  
  