using the Adjacency Model and or Nested Sets Model within CI4

Hi guys,

I am looking at the best way to CRUD the following kind of data

Code:

+-------------+----------------------+--------+-----+-----+
| category_id | name                 | parent | lft | rgt |
+-------------+----------------------+--------+-----+-----+
|           1 | WIDGET A             |   NULL |   1 |  20 |
|           2 | WIDGET B             |      1 |   2 |   9 |
|           3 | WIDGET C             |      2 |   3 |   4 |
|           4 | WIDGET D             |      2 |   5 |   6 |
|           5 | WIDGET E             |      2 |   7 |   8 |
|           6 | WIDGET F             |      1 |  10 |  19 |
|           7 | WIDGET G             |      6 |  11 |  14 |
|           8 | WIDGET H             |      7 |  12 |  13 |
|           9 | WIDGET I             |      6 |  15 |  16 |
|          10 | WIDGET J             |      6 |  17 |  18 |
+-------------+----------------------+--------+-----+-----+

In my use case every time i add a new widget I would want to add the widget to a parent and then update the lft,rgt values of each widget inside the parent widgets lft/rgt range.

My main concern in this is that i expect to have over a million widgets all at varying parents with encompasing lft/rgt widgets underneath.

Adding new widgets sounds like its going to be expensive to do and this is the thing that will be done very regularly. New widgets may be added within seconds of each other or even in the same second.

Reading widgets is also a concern.
When it comes to reading widgets, I would like to create a list of all widgets underneath a widget.

For example if i wanted all widgets under WIDGET A i would expect to get something back thats able to translate to (using the parent and lft/rgt columns to build out the below strucutre)

Code:

WIDGET A
|
+---+ WIDGET B ---+ WIDGET C
    |             |
    |             + WIDGET D
    |             |
    |             + WIDGET E
    |
    + WIDGET F ---+ WIDGET G --- + WIDGET H
                  |
                  + WIDGET I
                  |
                  + WIDGET J

What would be the best ways to approach this using CI4? (is this even the best approach?) - The reading widgets part for example would normally be done using memory tables because it is quite an intensive thing to query the database for.

The biggest concern is that WIDGET A could potentially hold millions of widgets under it and need reading regularly.

Any help/guidance would be greatly appreciated!

Use a Nested Set Model that you can install using composer.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Thanks, do you have any recommendations of which composer version to use?

I decided to go down this route instead.

https://www.percona.com/blog/2020/02/13/...on-part-2/

It seems to be working good with a subset of data thats over 100k rows.