SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Feb 2006
    Location
    Rome, Italy
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question positions in adjacency model hierarchy

    I'm trying to learn something about hierarchical data storage and retrievement with php-mysql and I was wondering whether it is possible to retrieve a whole indented tree with a single query having a table like this - itemID, itemName, parentID, itemPosition - where itemPosition is the position between siblings.
    Hope someone understand what I mean...

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    please provide some sample rows. i don't understand wha itemPosition is for.

  3. #3
    SitePoint Member
    Join Date
    Feb 2006
    Location
    Rome, Italy
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll use something similar to some data I found as an example to learn from a tutorial.

    id | name | par | pos
    1 | fruit | 0 | 1
    2 | yellow | 1 | 1
    3 | banana | 2 | 2
    4 | red | 1 | 2
    5 | strawberry | 4 | 1
    6 | lemon | 2 | 1
    7 | cherry | 4 | 2
    8 | melon | 2 | 3

    Which should give an indented tree like this:

    fruit
    - yellow
    -- lemon
    -- banana
    -- melon
    - red
    -- strawberry
    -- cherry

    Here, apart from the id, the position of yellow fruits is:
    1. lemon
    2. banana
    3. melon

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that's not possible with one query if your tree has an unlimited depth (width). if your depth (width) has a fixed maximum, it requires the table to be self joined that many times minus one.

  5. #5
    SitePoint Member
    Join Date
    Feb 2006
    Location
    Rome, Italy
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You mean something like:
    SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3
    FROM tarta_recu AS t1
    LEFT JOIN tarta_recu AS t2 ON t2.parent = t1.id
    LEFT JOIN tarta_recu AS t3 ON t3.parent = t2.id
    WHERE t1.name = 'fruit';

    But I don't understand how to "order by" position, and I guess you should group data in some way... Maybe I'm touching a difficult point for a learner...
    (Of course I didn't write the query by mysefl - found it in a good tutorial on dev.mysql.com).

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT t1.name AS lev1
         , t1.pos AS pos1
         , t2.name as lev2
         , t2.pos as pos2
         , t3.name as lev3 
         , t3.pos as pos3 
      FROM tarta_recu AS t1
    LEFT outer
      JOIN tarta_recu AS t2 
        ON t2.parent = t1.id
    LEFT outer
      JOIN tarta_recu AS t3 
        ON t3.parent = t2.id
     WHERE t1.name = 'fruit'
    order
        by t1.pos 
         , t2.pos 
         , t3.pos
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Feb 2006
    Location
    Rome, Italy
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, longneck and r937 - you were very kind, really... I'll keep on trying and studing these next days...


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
  •