Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Harro, question on the ORM nestedset using for a Menu
  • I have a drag & drop menu that I wrote for CI along time ago and I think that it would benifit form the new ORM nestedset. see the table structures below:

    CREATE TABLE IF NOT EXISTS `menu_groups` (
      `id`        int(11)      unsigned NOT NULL  AUTO_INCREMENT,
      `menu_name` varchar(255)          NOT NULL,
      `menu_slug` varchar(255)          NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

    -- --------------------------------------------------------

    CREATE TABLE IF NOT EXISTS `menu_items` (
      `id`              int(11)      unsigned NOT NULL  AUTO_INCREMENT,
      `lft`             int(11)      unsigned NOT NULL,
      `rgt`             int(11)      unsigned NOT NULL,
      `menu_id`         int(11)      unsigned NOT NULL,
      `item_title`      varchar(255)          NOT NULL,
      `title_slug`      varchar(255)          NOT NULL,
      `item_heading`    varchar(255)                    DEFAULT NULL,
      `item_class`      varchar(255)          NOT NULL,
      `item_type`       varchar(12)           NOT NULL,
      `item_content`    varchar(255)          NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

    My question is this, can I convert both tables into one using the nestedset using the tree_id for the top menu table, or should I keep the top menu table and relate it to the nesetedset assiging the id to the tree_id ?



  • HarroHarro
    Accepted Answer
    Without knowing how exactly you want to implement it, an answer is difficult.

    But assuming you want to store a complete menu system, it is not a problem to store that into a single table.

    In this case the root will be a dummy (or in the case of multiple trees in a single table, the root will be the tree identifier), the children of the root will be your main menu items (groups), children of those will be your menu items.

    This is how it's implemented in our applications too.
  • OK, thanks. That's just what I wanted to know.
  • How did you solve the drag & drop part on the backend side?

    I looked at Nestable as a frontend JQuery plugin, but it sends the whole tree to the server. How do you know which node you have to update? How do you compare the whole modified tree (actually only the ids and the children ids) with the original, and make the modifications and keep the number of SQL request as low as possible?

    Actually I do realise know, that I don't really understand this model yet, but I am getting better (I hope :D).
  • We already had custom JS for it (from when we used CI as backend and used Datamappers nestedsets ;)).

    We display the tree in a table, the JS code can drag-and-drop table rows, and when one is dropped, it will do an ajax call with the id of the row that is dropped, and the row it is dropped on. The rest controller then fetches both from the tree, and performs a move operation.

    I think you can do something similar with jQuery, but I have no experience with it.
  • Ok, but how do you know WHERE to put the node if there are many other nodes on the same level?

    Eg:

    ROOT(1)
    ---------MAIN(2)
    -----------------SUB1(3)
    -----------------SUB2(4)
    -----------------SUB3(5)
    ---------ITEM2(6)
    ------------------SUB4(7)
    ------------------SUB5(8)
    ------------------SUB6(9)

    You want to move SUB5(id: 8) between SUB2 and SUB3. What is "the row it is dropped on"?
  • Ah.

    Using your example, when you pickup SUB6 and move it up, a blank "placeholder" row is inserted on which you can drop it. If you move it, the placeholder moves with you. Say you drop it on the placeholder between SUB2 and SUB3, it will send a REST request to move 9 to next_sibling of 4.

    The second functionality is that you can grab a row, and move it horizontally. Say you grab SUB2 and move it left, it will send a REST request to move 4 as next_child() of 1 (which in the example will move it to the bottom, after ITEM2.

    If you have deeper nesting, you can move left more, so you can move more than a level up in one go.

    You can move right only once. Again, if you take SUB2 and move it right, it will send a REST request to move 4 as first_child() of 3.
  • A simple version of this mechanism (and our first attempt to convert this script into something jQuery) is implemented in the page edit section of Depot, using unordered lists instead of a table.
  • Hm. It sounds a bit complicated for me. And I don't really like the idea of having the client side decide what the server has to do.

    And there is a downside of this solution: every move means a save which means a request to the server which can make the application (JavaScript) a bit inconsistent if you are "too quick".

    This is why I chose this Nestable plugin: it sends the whole tree to the server and the server can decide what to do and also you can make multiple changes to the tree and save it by clicking on a button, etc. I think this approach is much more usable for the frontend.

    Of course this has a downside too: You have to "reindex" the whole tree (or maybe this can be solved by only modifying the moved rows), but this is not such a big problem for a menu where there is not much node.
  • As always, there are many ways to solve a problem. ;-)

    Our code is at least 5 years old, but it does do the job, so we haven't felt the need to change it.
  • Ok, I just wanted to hear your opinion about this solution as Nestedset is new to me.
  • Maybe you can check the Depot code, it uses nestedsets and nestedsortable for the documentation structure.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion