CODEWITHSUNDEEP
phpmysql
thecodedeveloper.com
thecodedeveloper.com

Reputation: 3240

Change in select query

I have Adjacency list mode structure like that

enter image description here

by this query

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';

getting result like that

enter image description here

but i want to get result like that, there is any option do it

enter image description here

thanks in advance

Upvotes: 3

Views: 162

Answers (4)

Cylindric
Cylindric

Reputation: 5894

As a point of interest, because it might be too much work and different from what you have to be an "answer", I would suggest you look into the concept of "Modified Pre Ordered Tree Traversal". There's a great explanation by Gijs Van Tulder.

It will require additional fields on the table to store the hierarchy data, and extra code to maintain that structure, but it can make tree-structures much easier to deal with in a relational database engine.

Basically, as well as the usual parent_id that we store with trees, we also store a left and a right value. When correctly populated, it's then easy to get "all children of node" by simply selecting all nodes where node.left > parent.left and node.right < parent.right, regardless of the depth of the nodes.

Upvotes: 1

DRapp
DRapp

Reputation: 48139

You'll need a UNION, and I suggest removing the duplicates of entries at their respective levels. Also, no need to include "NULL" values if your intention is to present to users as in web apps...

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"

This is obviously fixed to 4 levels but will union them into a single list, but no duplicates as you presented. I also tacked on the hierarchy level just for reference purposes. In order to get the counts at each level, you must do a LEFT JOIN to the next level, otherwise, you'll miss possible items at the level you are trying to retrieve, but the others SHOULD remain as INNER JOINs.

If you are dealing with products, I would think that if not after 4 levels deep they can't find something, there's a bigger problem :)

Upvotes: 1

Wesley van Opdorp
Wesley van Opdorp

Reputation: 14941

Unfortunately it's difficult to count the amount of sub-categories of a category with your current setup. Not only is the depth of your menu limited by the amount of LEFT JOIN's you add, it's also impossible to tell which categories are directly linked to a category more then one level deep.

A excellent solution to this problem is using a nested set structure for your menu, you can read more here. More explanation how to do this with mysql here.

Upvotes: 1

Edwin Bautista
Edwin Bautista

Reputation: 364

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'

Upvotes: 0

Related Questions