RESOLVED - MySql Nested Sets - Find "leaves"

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

I have the following table:

MYSQL Code: [ Select ]
CREATE TABLE task(
id INT(11) PRIMARY KEY NOT NULL REFERENCES `data`(id),
name VARCHAR(255) NOT NULL,
description TEXT NULL,
start_date DATETIME NULL,
end_date DATETIME NULL,
lft INT(11) NOT NULL,
rgt INT(11) NOT NULL
) ENGINE=InnoDB;
  1. CREATE TABLE task(
  2. id INT(11) PRIMARY KEY NOT NULL REFERENCES `data`(id),
  3. name VARCHAR(255) NOT NULL,
  4. description TEXT NULL,
  5. start_date DATETIME NULL,
  6. end_date DATETIME NULL,
  7. lft INT(11) NOT NULL,
  8. rgt INT(11) NOT NULL
  9. ) ENGINE=InnoDB;


and with this table I have the following data as an example:

Attachments:
data.gif

The words are the names and the red shows the "lft" and "rgt" values.

To find all the "leaf" nodes I run the following query:

MYSQL Code: [ Select ]
SELECT node.name
FROM task AS parent, task AS node
JOIN `data` ON node.id = `data`.id
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.rgt = (node.lft + 1)
AND `data`.status = 'A'
GROUP BY node.name
ORDER BY DATE(`data`.created_date),(COUNT(parent.name) - 1) DESC;
  1. SELECT node.name
  2. FROM task AS parent, task AS node
  3. JOIN `data` ON node.id = `data`.id
  4. WHERE node.lft BETWEEN parent.lft AND parent.rgt
  5. AND node.rgt = (node.lft + 1)
  6. AND `data`.status = 'A'
  7. GROUP BY node.name
  8. ORDER BY DATE(`data`.created_date),(COUNT(parent.name) - 1) DESC;


With that I would get the following as a result:
"Find Images", "Sign Off", "Blog", "User" and "Load Content".

Each of these "tasks" have a status of "A" (for Active) at this moment (which is found in the `data` table), but let's say "Blog" and "User" now both have a status of "F" (for Finished). I would then like to see the following as a result:
"Find Images", "Sign Off", "Load Modules", "Load Content".
So I would like to find all the leaf nodes, but if all the nodes in a node have a status of "F", I'd like to see that node and not the children.

Is it possible? And if it is do you know how to do it because I am lost.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

Can you export your table with the data? I'll play around with this a bit, but I want to make sure I have the correct data.

The child node values for left and right will always be between the parent left and right, correct?
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

Sure, here is the code:

MYSQL Code: [ Select ]
CREATE TABLE task(
id INT(11) PRIMARY KEY NOT NULL REFERENCES `data`(id),
name VARCHAR(255) NOT NULL,
description TEXT NULL,
start_date DATETIME NULL,
end_date DATETIME NULL,
lft INT(11) NOT NULL,
rgt INT(11) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE `data`(
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
created_date DATETIME NULL,
last_modified_date DATETIME NULL,
type VARCHAR(255) NOT NULL,
status CHAR(1) NOT NULL DEFAULT 'P',
url VARCHAR(255) NOT NULL,
view_count INT(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB;
INSERT INTO `data` VALUES(1,NOW(),NOW(),'Task','A','project',0),(2,NOW(),NOW(),'Task','A','design',0),(3,NOW(),NOW(),'Task','A','find_images',0),(4,NOW(),NOW(),'Task','A','sign_off',0),(5,NOW(),NOW(),'Task','A','develop',0),(6,NOW(),NOW(),'Task','A','load_modules',0),(7,NOW(),NOW(),'Task','A','blog',0),(8,NOW(),NOW(),'Task','A','user',0),(9,NOW(),NOW(),'Task','A','load_content',0);
INSERT INTO task VALUES(1,'Project',null,null,null,1,18),(2,'Design',null,null,null,2,7),(3,'Find Images',null,null,null,3,4),(4,'Sign Off',null,null,null,5,6),(5,'Develop',null,null,null,8,17),(6,'Load Modules',null,null,null,9,14),(7,'Blog',null,null,null,10,11),(8,'User',null,null,null,12,13),(9,'Load Content',null,null,null,15,16);
  1. CREATE TABLE task(
  2. id INT(11) PRIMARY KEY NOT NULL REFERENCES `data`(id),
  3. name VARCHAR(255) NOT NULL,
  4. description TEXT NULL,
  5. start_date DATETIME NULL,
  6. end_date DATETIME NULL,
  7. lft INT(11) NOT NULL,
  8. rgt INT(11) NOT NULL
  9. ) ENGINE=InnoDB;
  10. CREATE TABLE `data`(
  11. id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  12. created_date DATETIME NULL,
  13. last_modified_date DATETIME NULL,
  14. type VARCHAR(255) NOT NULL,
  15. status CHAR(1) NOT NULL DEFAULT 'P',
  16. url VARCHAR(255) NOT NULL,
  17. view_count INT(11) NOT NULL DEFAULT 0
  18. ) ENGINE=InnoDB;
  19. INSERT INTO `data` VALUES(1,NOW(),NOW(),'Task','A','project',0),(2,NOW(),NOW(),'Task','A','design',0),(3,NOW(),NOW(),'Task','A','find_images',0),(4,NOW(),NOW(),'Task','A','sign_off',0),(5,NOW(),NOW(),'Task','A','develop',0),(6,NOW(),NOW(),'Task','A','load_modules',0),(7,NOW(),NOW(),'Task','A','blog',0),(8,NOW(),NOW(),'Task','A','user',0),(9,NOW(),NOW(),'Task','A','load_content',0);
  20. INSERT INTO task VALUES(1,'Project',null,null,null,1,18),(2,'Design',null,null,null,2,7),(3,'Find Images',null,null,null,3,4),(4,'Sign Off',null,null,null,5,6),(5,'Develop',null,null,null,8,17),(6,'Load Modules',null,null,null,9,14),(7,'Blog',null,null,null,10,11),(8,'User',null,null,null,12,13),(9,'Load Content',null,null,null,15,16);
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

SpooF wrote:
The child node values for left and right will always be between the parent left and right, correct?

Yes, I got the example from here.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

I saw this thread a few hours ago and was perplexed. I was thinking about sub-queries but not sure where to go with it. After grabbing some lunch I think I have something though.

SQL Code: [ Select ]
SELECT node.name
FROM task AS parent, task AS node
JOIN `data` ON node.id = `data`.id
WHERE
  (
   node.lft BETWEEN parent.lft AND parent.rgt
   AND node.rgt = (node.lft + 1)
   AND `data`.STATUS = 'A'
  )
  OR
  (
   node.lft IN (SELECT lft - 1 AS _lft FROM task LEFT JOIN `data` ON task.id = `data`.id WHERE `data`.STATUS = 'F')
   AND node.rgt IN (SELECT rgt + 1 AS _rgt FROM task LEFT JOIN `data` ON task.id = `data`.id WHERE `data`.STATUS = 'F')
  )
GROUP BY node.name
ORDER BY DATE(`data`.created_date),(COUNT(parent.name) - 1) DESC
  1. SELECT node.name
  2. FROM task AS parent, task AS node
  3. JOIN `data` ON node.id = `data`.id
  4. WHERE
  5.   (
  6.    node.lft BETWEEN parent.lft AND parent.rgt
  7.    AND node.rgt = (node.lft + 1)
  8.    AND `data`.STATUS = 'A'
  9.   )
  10.   OR
  11.   (
  12.    node.lft IN (SELECT lft - 1 AS _lft FROM task LEFT JOIN `data` ON task.id = `data`.id WHERE `data`.STATUS = 'F')
  13.    AND node.rgt IN (SELECT rgt + 1 AS _rgt FROM task LEFT JOIN `data` ON task.id = `data`.id WHERE `data`.STATUS = 'F')
  14.   )
  15. GROUP BY node.name
  16. ORDER BY DATE(`data`.created_date),(COUNT(parent.name) - 1) DESC
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

Excellent. Thanks joebert, thanks SpooF. Resolved.
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

Okay, maybe not completely. I just checked this a little further. If I finish "Blog", "User", "Load Modules", "Load Content", "Find Images" and "Sign Off", then I should be left with "Design" and "Develop". I am however left with "Design", "Develop" and "Load Modules".

I changed the query to the following to sort this out:
MYSQL Code: [ Select ]
SELECT node.id,node.name
FROM task AS parent, task AS node
JOIN `data` ON node.id = `data`.id
WHERE
  (
   node.lft BETWEEN parent.lft AND parent.rgt
   AND node.rgt = (node.lft + 1)
   AND `data`.`status` = 'A'
  )
  OR
  (
   node.lft IN (SELECT lft - 1 AS _lft FROM task LEFT JOIN `data` ON task.id = `data`.id WHERE `data`.STATUS = 'F')
   AND node.rgt IN (SELECT rgt + 1 AS _rgt FROM task LEFT JOIN `data` ON task.id = `data`.id WHERE `data`.STATUS = 'F')
   AND `data`.`status` != 'F'
  )
GROUP BY node.name
ORDER BY DATE(`data`.created_date),(COUNT(parent.name) - 1) DESC
  1. SELECT node.id,node.name
  2. FROM task AS parent, task AS node
  3. JOIN `data` ON node.id = `data`.id
  4. WHERE
  5.   (
  6.    node.lft BETWEEN parent.lft AND parent.rgt
  7.    AND node.rgt = (node.lft + 1)
  8.    AND `data`.`status` = 'A'
  9.   )
  10.   OR
  11.   (
  12.    node.lft IN (SELECT lft - 1 AS _lft FROM task LEFT JOIN `data` ON task.id = `data`.id WHERE `data`.STATUS = 'F')
  13.    AND node.rgt IN (SELECT rgt + 1 AS _rgt FROM task LEFT JOIN `data` ON task.id = `data`.id WHERE `data`.STATUS = 'F')
  14.    AND `data`.`status` != 'F'
  15.   )
  16. GROUP BY node.name
  17. ORDER BY DATE(`data`.created_date),(COUNT(parent.name) - 1) DESC

It works with that also now.

Thanks a lot again guys.

Post Information

  • Total Posts in this topic: 7 posts
  • Users browsing this forum: No registered users and 61 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.