Geek asks and answers

Change selected query

I have an Adjacency list mode structure like this

enter image description here

by this request

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

get such a result

enter image description here

but I want to get such a result, is there any option

enter image description here

early

+3
source share
4 answers

Unfortunately, it’s hard to count the number of subcategories of a category with your current setting. Not only is the depth of your menu limited by the amount LEFT JOINyou add, it is also impossible to determine which categories are directly related to a category of more than one level.

- nested set , . , mysql .

+1

UNION, . , "NULL", , -...

select L1.Category_ID,
       L1.name, 
       "1" as HierarchyLevel, 
       count( L2.Category_ID ) as NextLevelCount
   from Category L1
        LEFT JOIN Category L2
           on L1.Category_ID = L2.Parent
   where L1.name = "ELECTRONICS"
   group by L1.Category_ID
UNION
select L2.Category_ID,
       L2.name, 
       "2" as HierarchyLevel,
       count( L3.Category_ID ) as NextLevelCount
   from Category L1
        JOIN Category L2
           on L1.Category_ID = L2.Parent
           LEFT JOIN Category L3
              on L2.Category_ID = L3.Parent
   where L1.name = "ELECTRONICS"
   group by L2.Category_ID
UNION
select L3.Category_ID,
       L3.name, 
       "3" as HierarchyLevel,
       count( L4.Category_ID ) as NextLevelCount
   from Category L1
        JOIN Category L2
           on L1.Category_ID = L2.Parent
           JOIN Category L3
              on L2.Category_ID = L3.Parent
              LEFT JOIN Category L4
                 on L3.Category_ID = L4.Parent
   where L1.name = "ELECTRONICS"
   group by L3.Category_ID
UNION
select L4.Category_ID,
       L4.name, 
       "4" as HierarchyLevel,
       1 as NextLevelCount
   from Category L1
        JOIN Category L2
           on L1.Category_ID = L2.Parent
           JOIN Category L3
              on L2.Category_ID = L3.Parent
              JOIN Category L4
                 on L3.Category_ID = L4.Parent
   where L1.name = "ELECTRONICS"

, , 4 , , , . . , LEFT JOIN , , , INNER JOINs.

, , 4 , -, :)

+1

, , "", " ". Gijs Van Tulder.

To store hierarchy data, additional fields and additional code are required to support this structure, but it can simplify the work with tree structures in a relational database.

In principle, like the usual parent_idone that we store with trees, we also store the value of leftand right. With proper filling, then it’s easy to get “all children node” by simply selecting all the nodes where node.left > parent.leftand node.right < parent.right, regardless of the depth of the nodes.

+1
source

You can do something like this:

SELECT name FROM category WHERE name = 'ELECTRONICS'

UNION

SELECT t2.name FROM t2 INNER JOIN category as t1 ON t2.parent = t1.category_id WHERE t1.name = 'ELECTRONICS'

UNION

SELECT t3.name FROM t3 INNER JOIN category as t1 ON t3.parent = t1.category_id WHERE t1.name = 'ELECTRONICS'

UNION

SELECT t4.name FROM t4 INNER JOIN category as t1 ON t4.parent = t1.category_id WHERE t1.name = 'ELECTRONICS'
0
source

More articles:


All Articles