Body: <p>I'm working with API4 and am unclear the syntax for a where clause (or if the following is possible). What I'd like to do is compare two custom fields in a filter for a Contact Get request. So something like this:</p>
  <pre><code>$contacts = \Civi\Api4\Contact::get(TRUE)
      -&gt;addJoin('Custom_MySet AS my_set', 'LEFT', ['id', '=', 'my_set.entity_id'])
      -&gt;addWhere('my_set.date_from', 'IS NOT EMPTY')
      -&gt;addWhere('my_set.date_to', '&lt;=', {my_set.date_from})
      -&gt;addWhere('is_deleted', '=', FALSE)
      -&gt;execute();
  </code></pre>
  <p>Note the &quot;{my_set.date_from}&quot; -- that's where I want to do a column comparison but am unclear the syntax required to reference another column/field rather than a static value.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-05-20T10:51:37.080Z
Comments.Text: Probably, in that SearchKit can do that with the fairly new 'field' option in where clauses [example](https://dmaster.demo.civicrm.org/civicrm/admin/search#/create/Contact?params=%7B%22version%22:4,%22select%22:%5B%22id%22,%22sort_name%22,%22contact_type:label%22,%22contact_sub_type:label%22%5D,%22orderBy%22:%7B%7D,%22where%22:%5B%5B%22constituent_information.Marriage_Date%22,%22%3D%22,%22created_date%22,true%5D%5D,%22groupBy%22:%5B%5D,%22join%22:%5B%5D,%22having%22:%5B%5D%7D) but Apiv4 Explorer doesn't offer that.
Comments.UserDisplayName: Aidan
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-05-20T21:03:02.973Z
Comments.Text: I think that may work -- thanks!
Comments.UserDisplayName: lcdservices
Tags: <apiv4>
Title: Is it possible to do field comparison filter in API4?
ThreadText: # Is it possible to do field comparison filter in API4?
  
  (Asked by: username_0 on 2024-05-16)
  
  I'm working with API4 and am unclear the syntax for a where clause (or if the following is possible). What I'd like to do is compare two custom fields in a filter for a Contact Get request. So something like this:
  $contacts = \Civi\Api4\Contact::get(TRUE)
      ->addJoin('Custom_MySet AS my_set', 'LEFT', ['id', '=', 'my_set.entity_id'])
      ->addWhere('my_set.date_from', 'IS NOT EMPTY')
      ->addWhere('my_set.date_to', '<=', {my_set.date_from})
      ->addWhere('is_deleted', '=', FALSE)
      ->execute();
  
  Note the "{my_set.date_from}" -- that's where I want to do a column comparison but am unclear the syntax required to reference another column/field rather than a static value.
  
  **Tags:** apiv4
  
  > **Comments:**
  > **username_1**: Probably, in that SearchKit can do that with the fairly new 'field' option in where clauses [example](https://dmaster.demo.civicrm.org/civicrm/admin/search#/create/Contact?params=%7B%22version%22:4,%22select%22:%5B%22id%22,%22sort_name%22,%22contact_type:label%22,%22contact_sub_type:label%22%5D,%22orderBy%22:%7B%7D,%22where%22:%5B%5B%22constituent_information.Marriage_Date%22,%22%3D%22,%22created_date%22,true%5D%5D,%22groupBy%22:%5B%5D,%22join%22:%5B%5D,%22having%22:%5B%5D%7D) but Apiv4 Explorer doesn't offer that.  
  > **username_0**: I think that may work -- thanks!  
  
  ---
  
  ## 0 answers
  
  