Nesting categories for MySQL

Question

API returns the categories like so:

[1] => Array
        (
            [category_id] => 14308
            [parent_id] => 14308
            [level] => 0
        )

    [2] => Array
        (
            [category_id] => 179836
            [parent_id] => 14308
            [level] => 1
        )

        [3] => Array
            (
                [category_id] => 230022
                [parent_id] => 179836
                [level] => 2
            )

And I need to insert them in the DB like so:

╔═════════════╦═══════════╗
║ category_id ║ parent_id ║
╠═════════════╬═══════════╣
║       14308 ║         0 ║
║      179836 ║     14308 ║
║      230022 ║    179836 ║
╚═════════════╩═══════════╝

... which is easy, but the category_to_path table is tricky which I can't seem to figure out. It needs to be like this:

╔═════════════╦═════════╦═══════╗
║ category_id ║ path_id ║ level ║
╠═════════════╬═════════╬═══════╣
║       14308 ║   14308 ║     0 ║
║      179836 ║   14308 ║     0 ║
║      179836 ║  179836 ║     1 ║
║      230022 ║   14308 ║     0 ║
║      230022 ║  179836 ║     1 ║
║      230022 ║  230022 ║     2 ║
╚═════════════╩═════════╩═══════╝

This is what I have so far:

$path_ids;
for ($i=0; $i <= ($category->CategoryLevel-1); $i++) { 
// -1 cause the API returns a non zero-based level

  $path_ids[$i]['category_id'] = $category->CategoryID;

  if ($category->CategoryLevel-1 == $i) {
    $path_ids[$i]['path_id'] = $category->CategoryParentID[0];
  }
  elseif ($category->CategoryLevel-1) {
    // ?
  }

  $path_ids[$i]['level'] = $i;

}

Answer 1

I would try to rewrite array after obtaining it from the API so it can looks this:

[14308] => Array
(
    [parent_id] => 14308
    [level] => 0
)

[179836] => Array
(
    [parent_id] => 14308
    [level] => 1
)

[230022] => Array
(
    [parent_id] => 179836
    [level] => 2
)

When you get your array from the API you can process it with simple foreach loop.

$arrayFromApi = apiCallToObtainData();    
$newArray = [];
foreach($arrayFromApi as $value){
    $newArray[$value['category_id']] = [
        $value['parent_id'], 
        $value=['level'],
    ];
}

Now you can easily access parents so it shouldn't be difficult to achieve what you need.

Answer 2

The following query should give you the categories and paths. It does not give the levels, since your original data lacks any sort of row number. You might need to use some dynamic SQL if you want to get the levels.

The strategy I used is to cross join the categories, which generates all possible starting and ending points. It also generates some paths not supported by the table. To remove these unwanted paths, I check each path against a subquery to make sure that it could be created by traversing from category to path in 3 steps or less.

SELECT t1.category_id AS category_id,
       t2.category_id AS path_id
FROM yourTable t1
CROSS JOIN yourTable t2
INNER JOIN
(
    SELECT t1.category_id AS category_id_1,
           t2.category_id AS category_id_2,
           t3.category_id AS category_id_3
    FROM yourTable t1
    LEFT JOIN yourTable t2
        ON t1.parent_id = t2.category_id
    LEFT JOIN yourTable t3
        ON t2.parent_id = t3.category_id
) t2
    ON t1.category_id = t2.category_id_1 AND
       t2.category_id IN (t2.category_id_1, t2.category_id_2, t2.category_id_3)
ORDER BY category_id,
         path_id