SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How can I convert my database to a nested tree structure

    I started a tree structure database from scratch, and I thought I had it right, heres what it looks like:
    cc5c881221898f29efeaa0f8328653ae.png

    but there musta been a glitch in there somewhere because the categories aren't organised properly anymore. I don't know how to go about fixing this now. Also, I have other category tables which I'd like to convert into a heirarchical structure, but they are packed full of entries so building it up from scratch manually isn't an option. Its the lft and rgt part that would be too difficult for me to enter manually, I can put in parent ids though, and I know that I could make a script to organise the lft, rgt values based on the parent_id but don't wanna go reinventing the wheel here, is there a tool that'll do this automatically? How do you all deal with heirarchical databases? Is the left and right value approach the best way to do it?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by FrogWarrior View Post
    How do you all deal with heirarchical databases?
    with the adjacency model, as in Categories and Subcategories -- works fine up to 15 levels deep

    Quote Originally Posted by FrogWarrior View Post
    Is the left and right value approach the best way to do it?
    yes, if you truly have ~unlimited~ levels of depth in your hierarchy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I sorted it out with cakephp. It makes it pretty easy, it does all the reorganising for you when you insert or edit a row.

  4. #4
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    CakePHP makes it real easy to convert an adjacency model database to a nested one. You just feed the parent_id into cakes Save function and it puts in the right and left values for you. I just converted a huge adjacency model database to nested model with cake.

  5. #5
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    NMR Spectroscopy on a sitepoint page!

    I thought I'd never see that happen.

    My life is now complete and has come full circle!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •