How to Create Breadcrumb Using the Nested Set Data Model in MySQL

There are so many articles in the Internet have explained us that the Nested Set Data Model has many advantages (although it is more complex among the others) for handling the Hierarchical Data Model.

Some of the advantages of this Nested Set Data Model are:

  1. You can create unlimited levels and it only needs one query to get all the related path.
  2. Since it only need one query, then it is faster to get and display the full path.
  3. It is easy to manage the records (inserting, deleting, and moving record). We will focus on this item in this article using MySQL database.

The only one disadvantage of this model is it is more complex than the others. You have to be careful to manage all of the records (inserting, deleting, and moving), otherwise you will break all of the breadcrumb records!

I came across the following article: Using the Nested Set Data Model for Breadcrumb Links and I think this is the smartest way how you can create the independent breadcrumb for any websites which have not implemented it. Since that article only explained how to add and delete a row (breadcrumb), but had not explained more detail how can you move the certain breadcrumb from the current position to the new parent, then in this article I wrote, I will try to explain you step by step, how you can create the Stored Procedures in MySQL for inserting new breadcrumb, deleting breadcrumbs based on their parent, and also: Moving the Breadcrumb from the current location to the new parent. This will be helpful to manage your breadcrumb easily and quickly especially if the current location has many breadcrumb childs. If you do not handle this carefully then you will destroy all of your breadcrumb records in your table.

  1. Creating Table for Breadcrumb

    First of all, let’s create a table which we will use to store all the breadcrumb records using the following SQL query:

    CREATE TABLE `breadcrumblinks` (
      `Page_Title` VARCHAR(100) NOT NULL,
      `Page_URL` VARCHAR(100) NOT NULL,
      `Lft` int(4) NOT NULL,
      `Rgt` int(4) NOT NULL,
      PRIMARY KEY (`Page_Title`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
  2. Inserting New Breadcrumb

    Now let’s create a Stored Procedure for adding a new record to your breadcrumblinks table above:

    CREATE PROCEDURE `addnewbreadcrumb`(IN PageTitleParent VARCHAR(100), 
                                        PageTitle VARCHAR(100), 
                                        PageURL VARCHAR(100))
    GoodBye: BEGIN
    -- Need three parameters (PageTitleParent, PageTitle, and PageURL), 
    -- look at this line --> `Page_Title` = PageTitleParent);
    -- look at this line --> VALUES (PageTitle, PageURL, ParentLevel, (ParentLevel + 1));
    DECLARE ParentLevel INTEGER;
    DECLARE RecCount INTEGER;
    DECLARE CheckRecCount INTEGER;
    DECLARE MyPageTitle VARCHAR(100);
    
    SET ParentLevel = (SELECT Rgt FROM `breadcrumblinks` WHERE 
    `Page_Title` = PageTitleParent);
    
    SET CheckRecCount = (SELECT COUNT(*) AS RecCount FROM `breadcrumblinks` WHERE 
    `Page_Title` = PageTitle);
    	IF CheckRecCount > 0 THEN
    		SET MyPageTitle = CONCAT("The following Page_Title is already exists in database: ", PageTitle);
    		SELECT MyPageTitle;
    		LEAVE GoodBye;
      END IF;
    
    UPDATE `breadcrumblinks`
       SET Lft = CASE WHEN Lft > ParentLevel THEN
          Lft + 2
        ELSE
          Lft + 0
        END,
       Rgt = CASE WHEN Rgt >= ParentLevel THEN
          Rgt + 2
       ELSE
          Rgt + 0
       END
    WHERE  Rgt >= ParentLevel;
    
    SET RecCount = (SELECT COUNT(*) FROM `breadcrumblinks`);
    	IF RecCount = 0 THEN
    		-- this is for handling the first record
    		INSERT INTO `breadcrumblinks` (Page_Title, Page_URL, Lft, Rgt)
    					VALUES (PageTitle, PageURL, 1, 2);
    	ELSE
    		-- whereas the following is for the second record, and so forth!
    		INSERT INTO `breadcrumblinks` (Page_Title, Page_URL, Lft, Rgt)
    					VALUES (PageTitle, PageURL, ParentLevel, (ParentLevel + 1));
    	END IF;
    
    END
    

    Now let’s discuss that Stored Procedure (SP) above:
    Look at this following line:

    CREATE PROCEDURE `addnewbreadcrumb`(IN PageTitleParent VARCHAR(100), 
                                        PageTitle VARCHAR(100), 
                                        PageURL VARCHAR(100))
    

    We see that this SP has a name: addnewbreadcrumb, and it needs three parameters.
    They are:
    PageTitleParent: This parameter is the page title parent of your new breadcrumb,
    PageTitle: This parameter is the page title of your new breadcrumb,
    PageURL: This parameter is the page URL of your new breadcrumb.

    Here is how we can keep the ParentLevel value based on the PageTitleParent parameter using this following SQL:

    SET ParentLevel = (SELECT Rgt FROM `breadcrumblinks` WHERE 
    `Page_Title` = PageTitleParent);
    

    Before we add a new breadcrumb record, let’s check to the table whether the new record that we will insert has already exists or not using this following code (this is important in order to avoid the summing up the value of Lft and Rgt of the breadcrumb):

    SET CheckRecCount = (SELECT COUNT(*) AS RecCount FROM `breadcru.mblinks` WHERE 
    `Page_Title` = PageTitle);
    	IF CheckRecCount > 0 THEN
    		SET MyPageTitle = CONCAT("The following Page_Title is already exists in database: ", PageTitle);
    		SELECT MyPageTitle;
    		LEAVE GoodBye;
      END IF;
    

    That code will tell us, if the record already exists, then display the custom message: “The following Page_Title is already exists in database: {ThePageTitle}”, and then exit from that SP by jumping to GoodBye label before the BEGIN statement above.

    The rest of that code is for adding the new breadcrumb. Important to know, that I added the special handling for adding the first record if the table is still empty, and also for adding the second record and so forth. Please look at the following code:

    SET RecCount = (SELECT COUNT(*) FROM `breadcrumblinks`);
    	IF RecCount = 0 THEN
    		-- this is for handling the first record
    		INSERT INTO `breadcrumblinks` (Page_Title, Page_URL, Lft, Rgt)
    					VALUES (PageTitle, PageURL, 1, 2);
    	ELSE
    		-- whereas the following is for the second record, and so forth!
    		INSERT INTO `breadcrumblinks` (Page_Title, Page_URL, Lft, Rgt)
    					VALUES (PageTitle, PageURL, ParentLevel, (ParentLevel + 1));
    	END IF;
    

    So, if you want to add your new breadcrumb record, simply execute this addnewbreadcrumb SP.

  3. Removing or Deleting Breadcrumb

    Now let’s create another SP for deleting the certain breadcrumb based on the given PageTitle parameter. This SP only needs one parameter (PageTitle):

    CREATE PROCEDURE `deletebreadcrumbbasedonpagetitle` (IN `PageTitle` VARCHAR(100))
    BEGIN
    -- Need one parameter (PageTitle), look at the line: WHERE  Page_Title = PageTitle;
    DECLARE DeletedPageTitle VARCHAR(100);
    DECLARE DeletedLft INTEGER;
    DECLARE DeletedRgt INTEGER;
    
    SELECT `Page_Title`, `Lft`, `Rgt`
    INTO   DeletedPageTitle, DeletedLft, DeletedRgt
    FROM   `breadcrumblinks`
    WHERE `Page_Title` = PageTitle;
    
    DELETE FROM `breadcrumblinks`
    WHERE Lft BETWEEN DeletedLft AND DeletedRgt;
    
    UPDATE `breadcrumblinks`
       SET Lft = CASE WHEN Lft > DeletedLft THEN
                 Lft - (DeletedRgt - DeletedLft + 1)
              ELSE
                 Lft
              END,
           Rgt = CASE WHEN Rgt > DeletedLft THEN
                 Rgt - (DeletedRgt - DeletedLft + 1)
              ELSE
                 Rgt
              END
       WHERE Lft > DeletedLft
          OR Rgt > DeletedLft;
    END
    

    If you want to remove or delete the certain breadcrumb record, simply execute this deletebreadcrumbbasedonpagetitle SP.

  4. Moving Breadcrumb to Another Parent

    So far we have already had the 2 SPs for inserting and deleting breadcrumb records. How about moving breadcrumb? Good question! Now the most important thing that we have to handle is how we can move the certain breadcrumb from the current position to the new parent. This is very important, especially if you want to move the certain breadcrumb which have so many childs beneath it. So we will create the third SP as following:

    CREATE PROCEDURE `movebreadcrumb`(IN CurrentRoot VARCHAR(100), IN NewParent VARCHAR(100))
    BEGIN
    -- Need two parameters: (1) CurrentRoot, and (2) NewParent.
    DECLARE Origin_Lft INTEGER;
    DECLARE Origin_Rgt INTEGER;
    DECLARE NewParent_Rgt INTEGER;
    
    SELECT `Lft`, `Rgt`
    	INTO Origin_Lft, Origin_Rgt
    	FROM `breadcrumblinks`
    	WHERE `Page_Title` = CurrentRoot;
    SET NewParent_Rgt = (SELECT `Rgt` FROM `breadcrumblinks`
    	WHERE `Page_Title` = NewParent);
    UPDATE `breadcrumblinks` 
    	SET `Lft` = `Lft` + 
    	CASE
    		WHEN NewParent_Rgt < Origin_Lft
    			THEN CASE
    				WHEN Lft BETWEEN Origin_Lft AND Origin_Rgt
    					THEN NewParent_Rgt - Origin_Lft
    				WHEN Lft BETWEEN NewParent_Rgt	AND Origin_Lft - 1
    					THEN Origin_Rgt - Origin_Lft + 1
    				ELSE 0 END
    		WHEN NewParent_Rgt > Origin_Rgt
    			THEN CASE
    				WHEN Lft BETWEEN Origin_Lft	AND Origin_Rgt
    					THEN NewParent_Rgt - Origin_Rgt - 1
    				WHEN Lft BETWEEN Origin_Rgt + 1 AND NewParent_Rgt - 1
    					THEN Origin_Lft - Origin_Rgt - 1
    				ELSE 0 END
    			ELSE 0 END,
    	Rgt = Rgt + 
    	CASE
    		WHEN NewParent_Rgt < Origin_Lft
    			THEN CASE
    		WHEN Rgt BETWEEN Origin_Lft AND Origin_Rgt
    			THEN NewParent_Rgt - Origin_Lft
    		WHEN Rgt BETWEEN NewParent_Rgt AND Origin_Lft - 1
    			THEN Origin_Rgt - Origin_Lft + 1
    		ELSE 0 END
    		WHEN NewParent_Rgt > Origin_Rgt
    			THEN CASE
    				WHEN Rgt BETWEEN Origin_Lft AND Origin_Rgt
    					THEN NewParent_Rgt - Origin_Rgt - 1
    				WHEN Rgt BETWEEN Origin_Rgt + 1	AND NewParent_Rgt - 1
    					THEN Origin_Lft - Origin_Rgt - 1
    				ELSE 0 END
    			ELSE 0 END;
    END
    

    You will see that this SP needs two parameter as you can see from this following line:

    CREATE PROCEDURE `movebreadcrumb`(IN CurrentRoot VARCHAR(100), IN NewParent VARCHAR(100))
    

    They are:
    CurrentRoot: This is the name of the current page title that you want to move.
    NewParent: This is the name of the page title where the CurrentRoot will be moved to. In other words, this is the new parent page title where the CurrentRoot will be located after the moving process is successfully done.

    If you want to move the certain breadcrumb record, simply execute this movebreadcrumb SP.

  5. Retrieving Breadcrumb

    As I mentioned above, you only need one query to get the full path of the certain given page.
    Here is one of the example of the code how you can retrieve the certain breadcrumb of the (e.g) ‘Statistik per Jam’ page:

    SELECT C.* FROM `breadcrumblinks` AS B, `breadcrumblinks` AS C
    WHERE (B.Lft BETWEEN C.Lft AND C.Rgt)
    AND (B.Page_Title = 'Statistik per Jam')
    ORDER BY C.Lft;
    

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>