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
  
  