Count parents backwords in one SQL Query?

Question

I have a MySQL database (created by Wordpress) and this is similar to what it looks like:

ID    parentID     otherStuff
54    55           this is a test
55    56           another test
56    0            last test

What I need to do is to check how deep down a page is. I know that when it reaches parentID 0 it's finished.

I could write 3 querys and then check when is equal to 0, but it would be more nice if it's possible with only 1 query. Is it possible? How?

Here is an example:

• ID: 56 has parent 0 and has DEPTH 0. (now 1 query)
• ID: 55 has parent 56 then 0 and has DEPTH 1. (now 2 querys)
• ID: 54 has parent 55 then 56 then 0 and has DEPTH 2. (now 3 querys)

I have solved it "the wrong way" (with one query each depth level) here get_depth()

The problem is that it's a recursive function and every depth requires one more query.

Answer 1

This is going to require a loop of some form to handle the arbitrary depth.

The loop might be in the form of procedural SQL (LOOP/LEAVE), or in your frontend code as you have written. The front-end will be slower due to the round trips to the database.

General solutions for handling Hierarchical MySQL data

Answer 2

If you want to query a tree in a relational database, use a nested set to represent the relationships. This will let you use a single query to find an item's depth, complete list of ancestors, find all related cases, and much more.

Answer 3

WITH RECURSIVE depths(id, parentId, depth) AS (
    SELECT stuff.id, stuff.parentId, 0 FROM stuff WHERE parentId = 0
  UNION
    SELECT stuff.id, stuff.parentId, depths.depth + 1
      FROM stuff INNER JOIN depths
        ON stuff.parentId = depths.id
) SELECT * FROM depths;

Of course, MySQL doesn't support SQL-99's WITH RECURSIVE, but you could definitely do the same thing iteratively. Maybe you should even keep a table with depths, or add a column into your existing table.

Answer 4

The type of table you have is called an Adjacency List. It is not possible in MySQL to have an arbitrary depth query on an adjacency list.

The usual cure for this (given you don't want to use nested sets and you have control of your db schema) is to store the depth of the node as a field in the node row (or store a delimited string that represents the path to the node). When you don't have control of the table structure, your best bet is to build a script that queries until it hits parent_id=0

Answer 5

You could write a stored proceedure that increments a counter and returns that instead of trying to perform this in a single query. Bottlenecks related to queries are usually on the client end anyway (transferring the data back and forth). Assuming the nesting doesn't get too deep, it shouldn't be a huge problem to do something like this:

CREATE FUNCTION get_nested_count( start INT ) RETURN INT
BEGIN
    DECLARE count INT 0;
    DECLARE current INT;
    current = start;
    DO
        count = count + 1;
        SELECT * FROM pages where "id"=current;
        current = pages.parent
    WHILE( pages.parent > 0 && pages.parent != start );
    RETURN count;
END

The second check on the while prevents circular looping (hopefully :P)

Answer 6

You could do something like this. It is not complete but this might give you an idea how to solve your problem using Common Table Expressions in SQL Server.

WITH [CTE]
  AS ( SELECT * FROM TieredTable t1
      UNION ALL
     SELECT t1.* FROM [CTE] cte, TieredTable t1
         WHERE t1.[Parent_Id] = cte.[Id] )
SELECT COUNT(*) AS cnt, id 
  FROM [CTE] 
 WHERE parent_id <> 0
 GROUP BY id

Here's the table;

CREATE TABLE [dbo].[TieredTable](
    [id] [int] NULL,
    [parent_id] [int] NULL,
    [stuff] [varchar](50) NULL
) 

id      pid     stuff
10  0 One
20  10 Two
30  20 Three

Answer 7

I found a very easy way to solve it, even without SQL. In my case I'm using Wordpress which have many tags and classes.

<?php $depth = count($post->ancestors); echo $depth; ?>

Simple but it works.

Your solutions on this problem works on more than just Wordpress, if they work. Should I set my solution as the right one, what do you think?

Answer 8

This query will return you depth level for any given node:

SELECT  COUNT(*)
FROM    (
        SELECT  id,
                @r :=
                (
                SELECT  parent
                FROM    mytable
                WHERE   id = @r
                        AND id <> 0
                ) AS _parent
        FROM    (
                SELECT  @r := 56
                ) vars,
                mytable
        WHERE   @r IS NOT NULL
        ) q
WHERE   _parent IS NOT NULL;

Replace @r := 56 with the node id you want.