CODEWITHSUNDEEP
sqldatabasepostgresql
Ringga Gustavino
Ringga Gustavino

Reputation: 11

How to update parent data triggered by child data?

I have a user database that have a user_id, parent and type column in it. I wanted to change the type of the parent row according to what the child is changing to lets say

Example table

+---------+------+--------+
| user_id | type | parent |
+---------+------+--------+
|    1    |  L1  |  null  |
+---------+------+--------+
|    2    | null |    1   |
+---------+------+--------+
|    3    | null |    1   |
+---------+------+--------+
|    4    | null |    2   |
+---------+------+--------+
|    5    | null |    2   |
+---------+------+--------+
|    6    | null |    5   |
+---------+------+--------+

if user_id 2 and 3 changes type to L1

Expected result

+---------+------+--------+
| user_id | type | parent |
+---------+------+--------+
|    1    |  L2  |  null  |
+---------+------+--------+
|    2    |  L1  |    1   |
+---------+------+--------+
|    3    |  L1  |    1   |
+---------+------+--------+
|    4    | null |    2   |
+---------+------+--------+
|    5    | null |    2   |
+---------+------+--------+
|    6    | null |    5   |
+---------+------+--------+

I want user_id 1 type to change into L2. The changes of the child can be in a separate time, maybe today user_id 2 is L1 but user_id 3 is still null then user_id 1 will also still be null. And when user_id 3 changes into L1 maybe the next day, user_id 1 changes into L2.

and if user_id 4 and 5 changes into L1, then user_id 2 type changes into L2 and user_id 1 changes into L3.

Expected result 2

+---------+------+--------+
| user_id | type | parent |
+---------+------+--------+
|    1    |  L3  |  null  |
+---------+------+--------+
|    2    |  L2  |    1   |
+---------+------+--------+
|    3    |  L1  |    1   |
+---------+------+--------+
|    4    |  L1  |    2   |
+---------+------+--------+
|    5    |  L1  |    2   |
+---------+------+--------+
|    6    | null |    5   |
+---------+------+--------+

All I need is a way to change the parent if the child changes its status. Because the parent changes its status, the grand parent can also change its status because the child of the grandparent changes its status. How do I trigger the grand parent or great great great grand parent to change if one node change is affecting the other levels above it.

I'm very confused of how to take the approach. Do I use this model or do I use column with array of user_id to resemble the grand parent

for example parent column

Table alternative approach

+---------+------+--------+
| user_id | type | parent |
+---------+------+--------+
|    1    |  L3  |  null  |
+---------+------+--------+
|    2    |  L2  |   [1]  |
+---------+------+--------+
|    3    |  L1  |   [1]  |
+---------+------+--------+
|    4    |  L1  |  [1,2] |
+---------+------+--------+
|    5    |  L1  |  [1,2] |
+---------+------+--------+
|    6    | null | [1,2,5]|
+---------+------+--------+

Because the tree is gonna be deep and I don't really know how many level will it be. I think the best way is to use recursive, but I don't really have any idea of how I'm going to do that for every update with the data changing constantly. Should I maybe create another table or something to make the query less deep and complex.

Upvotes: 1

Views: 132

Answers (1)

Oleg Komarov
Oleg Komarov

Reputation: 370

As a naive solution that may or may not work with your workload, you can start with something like this:

  • change your encoding of levels from null/L1/L2,... to 0/1/2,...
  • create a compound index on (parent, level)
  • decide if you want to store the path_to_root (as you do in your alternative table layout, but in reverse order, starting from the nearest parent), i.e. if your hierarchy isn't stable, then propagating the hierarchy changes may take more effort than level changes
  • every level update goes into a single transaction that does this:
# your actual change
cur.execute("update users set type = ? where user_id = ?", (new_type, user_id))

# cascading the changes upwards and hoping for an early exit
for ancestor in path_to_root:
    cur.execute(
        "update users
         set type = 1 + coalesce(children.min_type, 0)
         from (select min(type) as min_type from users where parent = ?) as children
         where user_id = ?", (ancestor, ancestor))
    if cur.rowcount == 0:
        break
conn.commit()

Upvotes: 0

Related Questions