SQL query to match a comma-separated string against a comma-separated string?

Question

The MySQL query below uses PHP to pull in the $sector, which is a single digit, and the $subsector_text, which is a comma separated string. The $subsector_text could be a single digit or a list of several IDs, such as "3,4,7,9".

  $sql = "
SELECT DISTINCT a.id
              , a.name
              , a.category_id
              , a.sector
              , a.subsector
              , a.year_in_operation
              , a.state
              , a.total_value
              , b.country_id
              , b.project_id
              , c.isocode_3
              , c.name
           FROM com_barchan_project a
           JOIN com_barchan_location b
             ON b.project_id = a.id
           JOIN com_barchan_country c
             ON c.id = b.country_id
           JOIN com_barchan_project_value_join d
             ON a.id = d.project_id
          WHERE a.state = 1 
            AND a.sector = '$sector'
            AND a.subsector REGEXP '^{$subsector_text}[,]|[,]{$subsector_text}[,]|[,]{$subsector_text}$|^{$subsector_text}$'
          ORDER 
             BY a.total_value DESC
              , a.category_id ASC
              , a.name ASC
";

The problem I'm having with the query above is with the line:

AND a.subsector REGEXP '^{$subsector_text}[,]|[,]{$subsector_text}[,]|[,]{$subsector_text}$|^{$subsector_text}$'  

If the $subsector_text = "3,4,5,9", then it's only returning records that contain exactly "3,4,5,9" in the $subsector field.

The desired result is that it would return any record that has any of the values in the $subsector_text. For instance, all these should be returned, but are currently not. This list is an example and by no means exact.

1,3
1,5
1,3,7,9
3,5
3,4,5,9
9
3
5
4

How do I change the query to select any records that has a value in that's in the $subsector_text string?

Please NOTE: That if the $subsector_text = 11, then the following, as an example, should not be selected.

1
12
21

Any help would be greatly appreciated.

Answer 1

It's not practical to match any value in a comma-separate string against any value in another comma-separated string in a single predicate.

You can use FIND_IN_SET() to search for one value at a time.

This means you need multiple predicates, one for each value you get by splitting your input $subsector_text. So split your variable and map it into a series of FIND_IN_SET() calls.

I haven't tested the following code, but it should give you the idea of what I'm talking about:

$subsector_array = array_map('intval', explode(',', $subsector_text));
$subsector_terms = array_map(
  function ($id) { return "FIND_IN_SET($id, a.subsector)"; },
  $subsector_array);
$subsector_expr = implode(' OR ', $subsector_terms);

$sql = "
SELECT ...
          WHERE a.state = 1 
            AND a.sector = '$sector'
            AND ($subsector_expr)
...";

This will of course force a table-scan because there's no way to index FIND_IN_SET(), or any other operation that searches for substrings. Well, I suppose your conditions on a.state and a.sector will use an index to narrow down the search before applying the FIND_IN_SET() conditions.

I understand the dilemma of having to work with a system that you inherited. Let your manager know that this needs to get refactored at some point, because it will never be efficient or reliable the way it's designed now.

Answer 2

Your approach is correct, but need some modifications. Instead of try to match in only one condition (REGEXP), can create multiple conditions joined with OR...

Example:

$subsectorArray = explode(',', $subsector_text);
$or = [];
foreach ($subsectorArray as $subsector){
    $or[] = "a.subsector REGEXP '[^[:alnum:]]{$subsector}[^[:alnum:]]|^{$subsector}[^[:alnum:]]|[^[:alnum:]]{$subsector}$|^{$subsector}$'";
}
$orStr = implode(' OR ', $or);

 $sql = "
SELECT DISTINCT a.id
              , a.name
              , a.category_id
              , a.sector
              , a.subsector
              , a.year_in_operation
              , a.state
              , a.total_value
              , b.country_id
              , b.project_id
              , c.isocode_3
              , c.name
           FROM com_barchan_project a
           JOIN com_barchan_location b
             ON b.project_id = a.id
           JOIN com_barchan_country c
             ON c.id = b.country_id
           JOIN com_barchan_project_value_join d
             ON a.id = d.project_id
          WHERE a.state = 1 
            AND a.sector = '$sector'
            AND ($orStr)
          ORDER 
             BY a.total_value DESC
              , a.category_id ASC
              , a.name ASC
";

Answer 3

The solution was to refactor the app. It took a couple days, but the offending code is gone and a new subsector table was created. Thanks everyone.