SQLite- Normalizing a concatenated field and joining with it?

Question

I have some data stored in comma-separated values in a field and I want to turn those comma-separated values into a temporary table and use those to join to another table

CREATE TABLE STRATEGY (STRATEGY_ID INTEGER PRIMARY KEY, APPLIED_SET_IDS VARCHAR);

CREATE TABLE ACTION_SET (APPLIED_ACTION_SET_ID INTEGER PRIMARY KEY, VALUE VARCHAR);

+-----------+---------------+
|STRATEGY_ID|APPLIED_SET_IDS|
+-----------+---------------+
|          1|1,3,6,7        |
|          2|1,2,4          |

+---------------------+-----+
|APPLIED_ACTION_SET_ID|VALUE|
+---------------------+-----+
|                    1|X    |
|                    2|Y    |
|                    3|Z    |
|                    4|H    |
|                    5|I    |
|                    6|J    |
|                    7|K    |
|                    8|L    |

I know I have to use some form of recursion as shown here. But every attempt I've done has made my head spin a bit. And my temporary table needs to preserve the original concatenated order of APPLIED_SET_ID values as well, like this...

+-----------+-----+--------------+
|STRATEGY_ID|ORDER|APPLIED_SET_ID|
+-----------+-----+--------------+
|          1|    1|             1|
|          1|    2|             3|
|          1|    3|             6|
|          1|    4|             7|
|          2|    1|             1|
|          2|    2|             2|
|          2|    3|             4|

Ultimately, I will join this table to the second existing table and use GROUP_CONCAT to replace the ID's with the corresponding values in the same order.

+-----------+------------------+
|STRATEGY_ID|APPLIED_SET_VALUES|
+-----------+------------------+
|          1|X,Z,J,K           |
|          2|X,Y,H             |

So regular expressions are out thanks to the order (otherwise I could have turned the commas to pipes and joined on a REGEXP statement). How can I achieve this? I know this is not normalized but I need to work with this current structure. Thank you for any help in advance.

Answer 1

It is possible to call PHP functions from SQLite. It makes it possible to use simple queries to 'normalize' the table.

I have converted the 'normalize comma delimited strings holding keys' to SQLite. see: (joining on ';' separated values in a column) for a more complete explanation

I started out looking for ways of converting the functions to run in SQLite. After some searching I came across this: Working with PHP UDFs in SQLite.

Which I found interesting - call PHP functions from SQLite. That sounds like fun!

It works but you cannot use PDO. You have to use the SQLite functions directly. Vendor Specific Database Extensions: SQLite3

Updated with your data (see previous edits for working code for other question)

The code:

<?php // Q34231542 -- count_in_set, value_in_set

/*
 * See this question for a rather more complete explanation of what this is doing...
 *
 * https://stackoverflow.com/questions/33782728/can-i-resolve-this-with-pure-mysql-joining-on-separated-values-in-a-column/
 */


define('SQLITE_DB', __DIR__ .'/Q34231542.sqlite');

/**
 * @var SQLite3
 */
$db = new SQLite3(SQLITE_DB);

/*
 * Define the functions for use by SQLite.
 */
$db->createFunction('count_in_set', 'count_in_set', 2);
$db->createFunction('value_in_set', 'value_in_set', 3);


$sql ="
SELECT STRATEGY.STRATEGY_ID                         as 'applied_strategy_id',
       STRATEGY.APPLIED_SET_IDS                     as 'applied_strategy_list',
       isequence.id                                 as 'which_strategy',
       COUNT_IN_SET(STRATEGY.APPLIED_SET_IDS, ',')  as 'StrategyCount',
       VALUE_IN_SET(STRATEGY.APPLIED_SET_IDS, ',', isequence.id)
                                                    as 'TheStrategy',
       ACTION_SET.VALUE                             as 'Action_Set_Value'


FROM STRATEGY
     JOIN integerseries AS isequence
       ON isequence.id <= COUNT_IN_SET(STRATEGY.APPLIED_SET_IDS, ',') /* normalize */

     JOIN ACTION_SET
       ON ACTION_SET.APPLIED_ACTION_SET_ID = VALUE_IN_SET(STRATEGY.APPLIED_SET_IDS, ',', isequence.id)
ORDER BY
       STRATEGY.STRATEGY_ID , ACTION_SET.APPLIED_ACTION_SET_ID;
";

/*
 * Run the query
 */
$stmt   = $db->prepare($sql);
$result = $stmt->execute();

/*
 * Get the results
 */
$rows = array();
while ($row = $result->fetchArray(SQLITE3_ASSOC)) { // fetch all the rows for now...
    $rows[] = $row;
}

/*
 * output...
 */
 // \Kint::dump($rows);
 echo '<pre>';
 var_dump($rows);
 echo '</pre>';

exit;

/* -------------------------------------------------------------------------
 *  The PHP functions called from SQLite
 */

/**
 * Count the number of delimited items in a string
 *
 * @param string $delimitedValues
 * @param string $delim
 * @return integer
 */
function count_in_set($delimitedValues, $delim)
{
    return substr_count(trim($delimitedValues, $delim), $delim) + 1;
}

/**
 * Treat the delimited values as ONE BASED array.
 *
 * @param string $delimitedValues
 * @param string $delim
 * @param integer $which
 * @return string
 */
function value_in_set($delimitedValues, $delim, $which)
{
    $items = explode($delim, $delimitedValues);
    return $items[$which - 1];
}

The output:

    applied_strategy_id applied_strategy_list which_strategy StrategyCount TheStrategy Action_Set_Value
#1  1                   "1,3,6,7"             1              4            "1"         "X"
#2  1                   "1,3,6,7"             2              4            "3"         "Z"
#3  1                   "1,3,6,7"             3              4            "6"         "J"
#4  1                   "1,3,6,7"             4              4            "7"         "K"
#5  2                   "1,2,4"               1              3            "1"         "X"
#6  2                   "1,2,4"               2              3            "2"         "Y"
#7  2                   "1,2,4"               3              3            "4"         "H"

The data:

CREATE TABLE [integerseries] (
  [id] INTEGER NOT NULL PRIMARY KEY);
INSERT INTO "integerseries" VALUES(1);
INSERT INTO "integerseries" VALUES(2);
INSERT INTO "integerseries" VALUES(3);
INSERT INTO "integerseries" VALUES(4);
INSERT INTO "integerseries" VALUES(5);
INSERT INTO "integerseries" VALUES(6);
INSERT INTO "integerseries" VALUES(7);
INSERT INTO "integerseries" VALUES(8);
INSERT INTO "integerseries" VALUES(9);
INSERT INTO "integerseries" VALUES(10);


CREATE TABLE STRATEGY (STRATEGY_ID INTEGER PRIMARY KEY, APPLIED_SET_IDS VARCHAR);
INSERT INTO "STRATEGY" VALUES(1,'1,3,6,7');
INSERT INTO "STRATEGY" VALUES(2,'1,2,4');

CREATE TABLE ACTION_SET (APPLIED_ACTION_SET_ID INTEGER PRIMARY KEY, VALUE VARCHAR);
INSERT INTO "ACTION_SET" VALUES(1,'X');
INSERT INTO "ACTION_SET" VALUES(2,'Y');
INSERT INTO "ACTION_SET" VALUES(3,'Z');
INSERT INTO "ACTION_SET" VALUES(4,'H');
INSERT INTO "ACTION_SET" VALUES(5,'I');
INSERT INTO "ACTION_SET" VALUES(6,'J');
INSERT INTO "ACTION_SET" VALUES(7,'K');
INSERT INTO "ACTION_SET" VALUES(8,'L');

Answer 2

My colleague developed a very clever solution, assuming the separator is a pipe | and not a comma ,.

He used REGEXP and the INSTR() function to get a numerical position, and that value drove the sorting.

SELECT STRATEGY_ID,
APPLIED_SET_IDS,
GROUP_CONCAT(VALUE,'|') as DESCRIPTION 

FROM ( 
    SELECT STRATEGY_ID,
    APPLIED_SET_IDS,
    CASE 
        WHEN APPLIED_ACTION_SET_ID = APPLIED_SET_IDS THEN 1
        WHEN instr(APPLIED_SET_IDS, APPLIED_ACTION_SET_ID || '|') = 1 Then 1
        WHEN instr(APPLIED_SET_IDS, '|' || APPLIED_ACTION_SET_ID || '|') > 0 Then instr(APPLIED_SET_IDS, '|' || APPLIED_ACTION_SET_ID || '|')
        ELSE 999999
    END AS APPLIED_ORDER,
    VALUE

    FROM STRATEGY 
    INNER JOIN ACTION_SET
    ON ACTION_SET.APPLIED_ACTION_SET_ID REGEXP '^(' || STRATEGY.APPLIED_SET_IDS || ')$'

    ORDER BY APPLIED_ORDER
) DESCRIPTIONS

GROUP BY 1,2

This gave me the exact output I was looking for.