Body: <p>It seems that there is some error with matches pattern</p>
  <p>It is not filling the sql correctly. I'm trying to find all contacts who traveled in years ending in 0 or 5</p>
  <p>^[0-9]*[05]$</p>
  <p>Here is the api version</p>
  <pre><code>{
    &quot;version&quot;: 4,
    &quot;select&quot;: [
      &quot;first_name&quot;,
      &quot;last_name&quot;,
      &quot;email_primary.email&quot;,
      &quot;Extra_IAA_Contact_Info.Primary_Cast:label&quot;
    ],
    &quot;orderBy&quot;: [],
    &quot;where&quot;: [
      [
        &quot;Extra_IAA_Contact_Info.Primary_Cast:name&quot;,
        &quot;REGEXP&quot;,
        &quot;\&quot;^[0-9]*[05]$&quot;
      ]
    ],
    &quot;groupBy&quot;: [],
    &quot;join&quot;: [],
    &quot;having&quot;: []
  }
  </code></pre>
  <p>And here is the generated SQL</p>
  <pre><code>SELECT `a`.`id` AS `id`, `a`.`first_name` AS `first_name`, `a`.`last_name` AS `last_name`, `email_primary_1`.`email` AS `email_primary.email`, `Extra_IAA_Contact_Info_2`.`primary_cast_6` AS `Extra_IAA_Contact_Info.Primary_Cast:label`
  FROM civicrm_contact a
  LEFT JOIN `civicrm_email` `email_primary_1` ON `a`.`id` =  `email_primary_1`.`contact_id` AND `email_primary_1`.`is_primary` = 1
  LEFT JOIN `civicrm_value_extra_iaa_con_1` `Extra_IAA_Contact_Info_2` ON `a`.`id` =  `Extra_IAA_Contact_Info_2`.`entity_id`
  WHERE (`Extra_IAA_Contact_Info_2`.`primary_cast_6` REGEXP &quot;&quot;)
    AND (`a`.`is_deleted` = &quot;0&quot;)
  ORDER BY `a`.`sort_name` ASC
  LIMIT 50
  OFFSET 0
  </code></pre>
  <p>I couldn't find any specific documentation on this, but I assume a standard mysql regexp would work. Notice in the sql the regexp is &quot;&quot;</p>
  <hr />
  <p>Doing more research, it appears that the  REGEXP operator isn't being handled correctly by the API.</p>
  <p>The field mapping is correct since using a = works.</p>
  <pre><code>SELECT `a`.`id` AS `id`, `a`.`first_name` AS `first_name`, `a`.`last_name` AS `last_name`, `email_primary_2`.`email` AS `email_primary.email`, `Extra_IAA_Contact_Info_1`.`primary_cast_6` AS `Extra_IAA_Contact_Info.Primary_Cast:label`
  FROM civicrm_contact a
  LEFT JOIN `civicrm_value_extra_iaa_con_1` `Extra_IAA_Contact_Info_1` ON `a`.`id` =  `Extra_IAA_Contact_Info_1`.`entity_id`
  LEFT JOIN `civicrm_email` `email_primary_2` ON `a`.`id` =  `email_primary_2`.`contact_id` AND `email_primary_2`.`is_primary` = 1
  WHERE (`Extra_IAA_Contact_Info_1`.`primary_cast_6` = &quot;100&quot;)
    AND (`a`.`is_deleted` = &quot;0&quot;)
  ORDER BY `a`.`sort_name` ASC
  </code></pre>
  <p>Update</p>
  <p>I'm reasonably sure the issue is with where clause. If I look at the output, with is one of, and manually enter all the lookups, it works, but in the sql it's mapping all id's not the actual name.</p>
  <pre><code>{
    &quot;version&quot;: 4,
    &quot;select&quot;: [
      &quot;first_name&quot;,
      &quot;last_name&quot;,
      &quot;email_primary.email&quot;,
      &quot;Extra_IAA_Contact_Info.Primary_Cast:label&quot;
    ],
    &quot;orderBy&quot;: [],
    &quot;where&quot;: [
      [
        &quot;Extra_IAA_Contact_Info.Primary_Cast:name&quot;,
        &quot;IN&quot;,
        [
          &quot;1965A&quot;,
          &quot;1965B&quot;,
          &quot;1965C&quot;,
          &quot;1970A&quot;,
          &quot;1970B&quot;,
          &quot;1970C&quot;,
          &quot;1970D&quot;,
          &quot;1975A&quot;,
          &quot;1975B&quot;,
          &quot;1975C&quot;,
          &quot;1975E&quot;,
          &quot;1980A&quot;,
          &quot;1980B&quot;,
          &quot;1980C&quot;,
          &quot;1980D&quot;,
          &quot;1980E&quot;,
          &quot;1985A&quot;,
          &quot;1985B&quot;,
          &quot;1985C&quot;,
          &quot;1985D&quot;,
          &quot;1985E&quot;,
          &quot;1990A&quot;,
          &quot;1990B&quot;,
          &quot;1990C&quot;,
          &quot;1990D&quot;,
          &quot;1990E&quot;,
          &quot;1995A&quot;,
          &quot;1995B&quot;,
          &quot;1995C&quot;,
          &quot;1995D&quot;,
          &quot;1995E&quot;,
          &quot;2000C&quot;,
          &quot;2000A&quot;,
          &quot;2000B&quot;,
          &quot;2000D&quot;,
          &quot;2000E&quot;,
          &quot;2005Fall&quot;,
          &quot;2005Spring&quot;,
          &quot;2010A&quot;,
          &quot;2010B&quot;,
          &quot;2015A&quot;,
          &quot;2015B&quot;,
          &quot;2020A&quot;
        ]
      ]
    ],
    &quot;groupBy&quot;: [],
    &quot;join&quot;: [],
    &quot;having&quot;: []
  }
  
  </code></pre>
  <pre><code>SELECT `a`.`id` AS `id`, `a`.`first_name` AS `first_name`, `a`.`last_name` AS `last_name`, `email_primary_2`.`email` AS `email_primary.email`, `Extra_IAA_Contact_Info_1`.`primary_cast_6` AS `Extra_IAA_Contact_Info.Primary_Cast:label`
  FROM civicrm_contact a
  LEFT JOIN `civicrm_value_extra_iaa_con_1` `Extra_IAA_Contact_Info_1` ON `a`.`id` =  `Extra_IAA_Contact_Info_1`.`entity_id`
  LEFT JOIN `civicrm_email` `email_primary_2` ON `a`.`id` =  `email_primary_2`.`contact_id` AND `email_primary_2`.`is_primary` = 1
  WHERE (`Extra_IAA_Contact_Info_1`.`primary_cast_6` IN (&quot;1&quot;, &quot;2&quot;, &quot;3&quot;, &quot;18&quot;, &quot;19&quot;, &quot;20&quot;, &quot;21&quot;, &quot;33&quot;, &quot;34&quot;, &quot;35&quot;, &quot;36&quot;, &quot;56&quot;, &quot;57&quot;, &quot;58&quot;, &quot;59&quot;, &quot;60&quot;, &quot;81&quot;, &quot;82&quot;, &quot;83&quot;, &quot;84&quot;, &quot;85&quot;, &quot;106&quot;, &quot;107&quot;, &quot;108&quot;, &quot;109&quot;, &quot;110&quot;, &quot;131&quot;, &quot;132&quot;, &quot;133&quot;, &quot;134&quot;, &quot;135&quot;, &quot;158&quot;, &quot;156&quot;, &quot;157&quot;, &quot;159&quot;, &quot;160&quot;, &quot;162&quot;, &quot;163&quot;, &quot;172&quot;, &quot;173&quot;, &quot;182&quot;, &quot;183&quot;, &quot;192&quot;))
    AND (`a`.`is_deleted` = &quot;0&quot;)
  ORDER BY `a`.`sort_name` ASC
  LIMIT 50
  OFFSET 0
  </code></pre>
  <p>As you can see, the API searches on</p>
  <p><code>Extra_IAA_Contact_Info.Primary_Cast:name</code></p>
  <p>but the sql is converted to the id, WHERE
  <code>(`Extra_IAA_Contact_Info_1`.`primary_cast_6` IN (&quot;1&quot;, &quot;2&quot;,...</code></p>
  <p>This is never going to work with regex as far as I can see. Maybe I'm missing something?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-11-26T00:47:51.260Z
Comments.Text: based on the fact searchKit added the `:name` suffix, it looks like your field has an option list. Are you trying to regex match the raw value of the field, or the name of the option.
Comments.UserDisplayName: Coleman
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-11-26T01:13:07.883Z
Comments.Text: Also you have an extra `\"` at the start of your regex - is that something you have in the definition or did searchkit add that itself?
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-11-26T17:06:56.403Z
Comments.Text: I didn't add the slash, but Coleman is correct, searching on name. It makes no sense to search on id's.
  
  I also used .*[05][A-Z]$
Comments.UserDisplayName: Jon Griffin
Tags: <searchkit>
Title: SearchKit Reg Broken
ThreadText: # SearchKit Reg Broken
  
  (Asked by: username_0 on 2024-11-25)
  
  It seems that there is some error with matches pattern
  
  It is not filling the sql correctly. I'm trying to find all contacts who traveled in years ending in 0 or 5
  
  ^[0-9]\*[05]$
  
  Here is the api version
  
  ```
  {
    "version": 4,
    "select": [
      "first_name",
      "last_name",
      "email_primary.email",
      "Extra_IAA_Contact_Info.Primary_Cast:label"
    ],
    "orderBy": [],
    "where": [
      [
        "Extra_IAA_Contact_Info.Primary_Cast:name",
        "REGEXP",
        "\"^[0-9]*[05]$"
      ]
    ],
    "groupBy": [],
    "join": [],
    "having": []
  }
  
  ```
  
  And here is the generated SQL
  
  ```
  SELECT `a`.`id` AS `id`, `a`.`first_name` AS `first_name`, `a`.`last_name` AS `last_name`, `email_primary_1`.`email` AS `email_primary.email`, `Extra_IAA_Contact_Info_2`.`primary_cast_6` AS `Extra_IAA_Contact_Info.Primary_Cast:label`
  FROM civicrm_contact a
  LEFT JOIN `civicrm_email` `email_primary_1` ON `a`.`id` =  `email_primary_1`.`contact_id` AND `email_primary_1`.`is_primary` = 1
  LEFT JOIN `civicrm_value_extra_iaa_con_1` `Extra_IAA_Contact_Info_2` ON `a`.`id` =  `Extra_IAA_Contact_Info_2`.`entity_id`
  WHERE (`Extra_IAA_Contact_Info_2`.`primary_cast_6` REGEXP "")
    AND (`a`.`is_deleted` = "0")
  ORDER BY `a`.`sort_name` ASC
  LIMIT 50
  OFFSET 0
  
  ```
  
  I couldn't find any specific documentation on this, but I assume a standard mysql regexp would work. Notice in the sql the regexp is ""
  
  ---
  
  Doing more research, it appears that the REGEXP operator isn't being handled correctly by the API.
  
  The field mapping is correct since using a = works.
  
  ```
  SELECT `a`.`id` AS `id`, `a`.`first_name` AS `first_name`, `a`.`last_name` AS `last_name`, `email_primary_2`.`email` AS `email_primary.email`, `Extra_IAA_Contact_Info_1`.`primary_cast_6` AS `Extra_IAA_Contact_Info.Primary_Cast:label`
  FROM civicrm_contact a
  LEFT JOIN `civicrm_value_extra_iaa_con_1` `Extra_IAA_Contact_Info_1` ON `a`.`id` =  `Extra_IAA_Contact_Info_1`.`entity_id`
  LEFT JOIN `civicrm_email` `email_primary_2` ON `a`.`id` =  `email_primary_2`.`contact_id` AND `email_primary_2`.`is_primary` = 1
  WHERE (`Extra_IAA_Contact_Info_1`.`primary_cast_6` = "100")
    AND (`a`.`is_deleted` = "0")
  ORDER BY `a`.`sort_name` ASC
  
  ```
  
  Update
  
  I'm reasonably sure the issue is with where clause. If I look at the output, with is one of, and manually enter all the lookups, it works, but in the sql it's mapping all id's not the actual name.
  
  ```
  {
    "version": 4,
    "select": [
      "first_name",
      "last_name",
      "email_primary.email",
      "Extra_IAA_Contact_Info.Primary_Cast:label"
    ],
    "orderBy": [],
    "where": [
      [
        "Extra_IAA_Contact_Info.Primary_Cast:name",
        "IN",
        [
          "1965A",
          "1965B",
          "1965C",
          "1970A",
          "1970B",
          "1970C",
          "1970D",
          "1975A",
          "1975B",
          "1975C",
          "1975E",
          "1980A",
          "1980B",
          "1980C",
          "1980D",
          "1980E",
          "1985A",
          "1985B",
          "1985C",
          "1985D",
          "1985E",
          "1990A",
          "1990B",
          "1990C",
          "1990D",
          "1990E",
          "1995A",
          "1995B",
          "1995C",
          "1995D",
          "1995E",
          "2000C",
          "2000A",
          "2000B",
          "2000D",
          "2000E",
          "2005Fall",
          "2005Spring",
          "2010A",
          "2010B",
          "2015A",
          "2015B",
          "2020A"
        ]
      ]
    ],
    "groupBy": [],
    "join": [],
    "having": []
  }
  
  ```
  
  ```
  SELECT `a`.`id` AS `id`, `a`.`first_name` AS `first_name`, `a`.`last_name` AS `last_name`, `email_primary_2`.`email` AS `email_primary.email`, `Extra_IAA_Contact_Info_1`.`primary_cast_6` AS `Extra_IAA_Contact_Info.Primary_Cast:label`
  FROM civicrm_contact a
  LEFT JOIN `civicrm_value_extra_iaa_con_1` `Extra_IAA_Contact_Info_1` ON `a`.`id` =  `Extra_IAA_Contact_Info_1`.`entity_id`
  LEFT JOIN `civicrm_email` `email_primary_2` ON `a`.`id` =  `email_primary_2`.`contact_id` AND `email_primary_2`.`is_primary` = 1
  WHERE (`Extra_IAA_Contact_Info_1`.`primary_cast_6` IN ("1", "2", "3", "18", "19", "20", "21", "33", "34", "35", "36", "56", "57", "58", "59", "60", "81", "82", "83", "84", "85", "106", "107", "108", "109", "110", "131", "132", "133", "134", "135", "158", "156", "157", "159", "160", "162", "163", "172", "173", "182", "183", "192"))
    AND (`a`.`is_deleted` = "0")
  ORDER BY `a`.`sort_name` ASC
  LIMIT 50
  OFFSET 0
  
  ```
  
  As you can see, the API searches on
  
  `Extra_IAA_Contact_Info.Primary_Cast:name`
  
  but the sql is converted to the id, WHERE
  `(`Extra_IAA_Contact_Info_1`.`primary_cast_6` IN ("1", "2",...`
  
  This is never going to work with regex as far as I can see. Maybe I'm missing something?
  
  **Tags:** searchkit
  
  > **Comments:**
  > **username_1**: based on the fact searchKit added the `:name` suffix, it looks like your field has an option list. Are you trying to regex match the raw value of the field, or the name of the option.  
  > **username_2**: Also you have an extra `\"` at the start of your regex - is that something you have in the definition or did searchkit add that itself?  
  > **username_0**: I didn't add the slash, but username_1 is correct, searching on name. It makes no sense to search on id's.  I also used .*[05][A-Z]$  
  
  ---
  
  ## 0 answers
  
  