Counting levels on adjacency based table 1

[Sleidia]

Hi

Does anyone know a single query that could successfully count the number of levels in a table based on the adjacency model?

By "number of levels", I mean the number of LEFT OUTER JOIN that needs to be used in a query such like this one :

SELECT level0.cat_ID AS level0_ID, level0.cat_parent_ID AS level0_parent_ID, level0.cat_name AS level0_name, level1.cat_ID AS level1_ID, level1.cat_parent_ID AS level1_parent_ID, level1.cat_name AS level1_name, level2.cat_ID AS level2_ID, level2.cat_parent_ID AS level2_parent_ID, level2.cat_name AS level2_name, level3.cat_ID AS level3_ID, level3.cat_parent_ID AS level3_parent_ID, level3.cat_name AS level3_name
FROM ecom_categories AS level0
LEFT OUTER JOIN ecom_categories AS level1 ON level1.cat_parent_ID = level0.cat_ID
LEFT OUTER JOIN ecom_categories AS level2 ON level2.cat_parent_ID = level1.cat_ID
LEFT OUTER JOIN ecom_categories AS level3 ON level3.cat_parent_ID = level2.cat_ID
WHERE level0.cat_parent_ID IS NULL
ORDER BY level0_name, level1_name, level2_name, level3_name

Thanks!

 

[Sleidia]

Ok, a recent post of r937 pointed me toward the almost perfect solution :

SELECT 
t1.cat_ID, t1.cat_parent_ID, t1.cat_name
FROM 
ecom_prod_cats AS t1 
LEFT JOIN 
ecom_prod_cats AS t2
ON 
t1.cat_ID = t2.cat_parent_ID
WHERE 
t2.cat_ID IS NULL 
AND 
t1.cat_parent_ID IS NOT NULL
GROUP BY 
t1.cat_parent_ID

... will work but I have to count the number of returned rows with PHP in order to get the total number of subcategories.

I've tried to use the COUNT(*) function like this :

SELECT 
COUNT(*) AS level_tot
FROM 
ecom_prod_cats AS t1 
LEFT JOIN 
ecom_prod_cats AS t2
ON 
t1.cat_ID = t2.cat_parent_ID
WHERE 
t2.cat_ID IS NULL 
AND 
t1.cat_parent_ID IS NOT NULL
GROUP BY 
t1.cat_parent_ID
LIMIT 1

... but it gives everything but the right result

Anyone knows how to succesfully use COUNT() with this query?

 

[r937]

may i ask why you need to know the number of levels?

r937.com | rudy.ca

 

[Sleidia]

Sure!

Actually, it's a follow-up of this very old thread :

http://www.tek-tips.com/viewthread.cfm?qid=1260402&page=1

If I have the number of levels, then, I can dynamically build the query that shows the tree structure.

 

[r937]

i'm not sure you're going to be able to run a simple query like your attempts above, just to find the number of levels

why not run, say, a 10-level self-join and see where it peters out?

presumably you're only doing this once? because if the number of levels can change (i.e. you have nothing to prevent level n+1 being added), then perhaps you had better start thinking about the nested set model again

r937.com | rudy.ca

 

[Sleidia]

hmm ... I don't get why adding levels would be a problem, especially when you count know the number of levels everytime you need to construct the final query (tree structure).

Well, if COUNT() can't really be used, the I'll just use the query above and count the number of rows returned. Not as elegant was I wished though

 

[r937]

to count rows in a query result, put the query as a subquery in the FROM clause like this --

select count(*) as rows
from (
your query goes here
) as results

but make sure you test your query thoroughly, though, because i'm not at all sure it counts levels correctly

r937.com | rudy.ca

 

[Sleidia]

... because i'm not at all sure it counts levels correctly

You're right!
The total number of different parent IDs is not equal to the number of levels. In fact, it's either equal or superior. Never inferior if I'm not mistaken. Thus, the query would still be usable for the query construction.

to count rows in a query result, put the query as a subquery in the FROM clause like this --

select count(*) as rows
from (
your query goes here
) as results

I've done this but it generates an error.

SELECT count(*) AS rows FROM (
SELECT 
t1.cat_ID, t1.cat_parent_ID, t1.cat_name, t1.lock_view  
FROM 
ecom_prod_cats AS t1 
LEFT JOIN 
ecom_prod_cats AS t2
ON 
t1.cat_ID = t2.cat_parent_ID
WHERE 
t2.cat_ID IS NULL 
AND 
t1.cat_parent_ID IS NOT NULL
GROUP BY 
t1.cat_parent_ID
) AS results

Error Code : 1064
You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 
t1.cat_ID, t1.cat_parent_ID, t1.cat_name, t1.lock_view
(0 ms taken)

Maybe this works only on more recent versions of mysql? I use 4.0.16 on Windows.

 

[r937]

yes, "more recent"

by the way, your version is several years old

that's like decades in dog years

r937.com | rudy.ca

 

[Sleidia]

You have something against oldness ? ahaha

Thanks for the info anyway