Geek Answers Handbook

The shortest line inside the same path (branch)

I have a tree view in the table mysql on the basis of id, depth, parent_idand path. Each root entry in this table has a depth of view 0, parent_id != nulland pathbased on the hexadecimal value of the identifier populated on the left with 0.

Each item in the tree created by specifying depth = parent.depth + 1, path = parent.path + hex(id), parent_id = parent.id(pseudo), eg:

id    path            depth    parent_id    assigned_user_id
------------------------------------------------------------
1     001             0        NULL         NULL
2     002             0        NULL         1
3     001003          1        1            2
4     002004          1        2            1
5     001003005       2        3            2
6     001003005006    3        5            2
7     002004007       2        4            1
8     002004008       2        4            2
9     002004009       2        4            2
10    00200400800A    3        8            2

and so on ... The problem is how to get entries for a specific user ID, limited to the shortest path in the same branch . For example, to assigned_user_id = 2extract:

id    path            depth    parent_id    assigned_user_id
------------------------------------------------------------
3     001003          1        1            2
8     002004008       2        4            2
9     002004009       2        4            2

Instead:

id    path            depth    parent_id    assigned_user_id
------------------------------------------------------------
3     001003          1        1            2
5     001003005       2        3            2
6     001003005006    3        5            2
8     002004008       2        4            2
9     002004009       2        4            2
10    00200400800A    3        8            2
+5
5
SELECT t1.*
FROM atable t1
  LEFT JOIN atable t2
    ON t2.assigned_user_id = t1.assigned_user_id AND
       t2.path = LEFT(t1.path, CHAR_LENGTH(t2.path)) AND
       t2.id <> t1.id
WHERE t1.assigned_user_id = 2
  AND t2.id IS NULL
+2

, , , parent_id . , , . , .

- :

SELECT * 
  FROM x 
  WHERE assigned_user_id = 2 
        AND parent_id NOT IN (SELECT id FROM x WHERE assigned_user_id = 2)

, ( - ):

  A1                    G2
 / \                   / \
B2  C2                H2  I2
    | \               |   | \
    D2  E2            L1  J2 K2
                      |
                      M2

B2, C2, G2 M2. , .

+2

- :

SELECT * FROM PATHS WHERE ASSIGNED_USER_ID = 2
AND NOT PARENT_ID IN (SELECT ID FROM PATHS WHERE ASSIGNED_USER_ID = 2)

, .

+1

: B , A, A B. , - , LIKE, " ".

SELECT a.* FROM node AS a
WHERE a.assigned_user_id = ?
AND NOT EXIST
(SELECT * FROM node AS b
    WHERE b.assigned_user_id = ?
    AND LENGTH(a.path) > LENGTH(b.path) 
    AND a.path LIKE CONCAT(b.path, '%') )

? .

_user_id. .

2nd EDIT

, b = a.

+1

- ?

select child.assigned_user_id, child.id
from node as child
left join node as parent
on child.path like CONCAT(parent.path, '%')
and child.assigned_user_id = parent.assigned_user_id
and child.id <> parent.id
group by child.assigned_user_id, child.id
having max(parent.id is null) = true

( , , , : , , , _user_id.)

0

More articles:


All Articles