[SOLVED] check if a child is a descendant using a single mysql query

check if a child is a descendant using a single mysql query

Suppose I have this table

parent | child 
   1       2        
   1       3        
   2       4        
   4       5        
   5       6

and I want to check if 6 is a descendant of 1 (which it is)....can I accomplish this within one single query, or will I have to inevitably get all data and process them with PHP?

Solution

You can walk the tree using recursion in MySQL without processing the data in PHP. MySQL doesn't support true recursion though, so you'd have to settle for some recursive-ish techniques:

One method is described in this SO question, but would involve changing your tables.

Another method would be by using a stored function, which will loop through the table until the job is done (which is recursive in a way). This would probably work with your current table, but is more complex to achieve.

I have an example right here that walks a TYPO3 pages table, finding all page ID's for a given rootline (comparable to what you're trying to achieve):

BEGIN
        DECLARE _id INT;
        DECLARE _parent INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;
        SET _id = -1;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(uid)
                INTO    @id
                FROM    pages
                WHERE   pid = _parent
                AND uid > _id
                AND deleted = 0
                AND hidden =0;                
                IF @id IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  uid, pid
                INTO    _id, _parent
                FROM    pages
                WHERE   uid = _parent
                AND     deleted = 0
                AND     hidden = 0;
        END LOOP;
END

Excuse me for not having the time right now to tailor this example to your needs, making this an incomplete answer, but I'm hoping this can get you on the right track nonetheless.