SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    The best way to retrieve Parent/Child Association?

    Hi,

    What will be the best way to retrieve this kind of records where parent=0 is the Parent and any others are sub-child of it:

    PHP Code:
    id  name       parent
    1   
    |  cat1        |  0
    2   
    |  sub-cat2  |  1
    3   
    |  sub-cat3  |  1
    4   
    |  cat4        |  
    End result preferably can be easily loop from programming layer like e.g:

    PHP Code:
    array( cat1 => array( sub-cat2sub-cat3 ),
              
    cat4 => array() ) 
    Thanks for any help.
    I Dunno LOL \(_o)/

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    SELECT * FROM daTable
    ORDER BY COALESCE(NULLIF(parent,0),id),id

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    SELECT * FROM daTable
    ORDER BY COALESCE(NULLIF(parent,0),id),id

    wooal this is something I haven't seen before!
    How does this coalesce actually works?

    Also I just added datetime column name "created", how do I order the list by created.desc?

    Thanks a bunch ruby!
    I Dunno LOL \(_o)/

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by justspree View Post
    How does this coalesce actually works?
    you have a copy of the mysql manual, right?

    Quote Originally Posted by justspree View Post
    how do I order the list by created.desc?
    Code:
    ORDER BY created DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried but doesn't seems to order correctly:

    SELECT * FROM daTable
    ORDER BY COALESCE(NULLIF(parent,0),id),id, created DESC
    I Dunno LOL \(_o)/

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you can sort them by hierarchical order as in your original request (see post #2), or you can sort them by created date (see post #4), but you can't sort them both ways at the same time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    erm so is:

    SELECT * FROM daTable
    ORDER BY COALESCE(NULLIF(parent,0),id),created desc
    ?Sorry I'm dumb
    I Dunno LOL \(_o)/

  8. #8
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I realized that I now have to retrieve parent=0 order by created desc first and then get the sub children to order by created.desc too like so:

    SELECT * FROM table where parent = 0 ORDER BY created.desc;

    while( $row = fetch( ) ) {

    SELECT * FROM table where parent = $row['id'] ORDER BY created.desc;
    }
    Is there a way to kill this to just 1 query?
    I Dunno LOL \(_o)/

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in order to do what you want, you would need a self-join

    could you please do a dump of the table, with enough rows of data to make a good test case

    that way i can actually test the query, rather than writing "air code"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Very much thanks for your help Rudy!

    CREATE TABLE `category` (
    `catID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` mediumtext NOT NULL,
    `parent` int(10) NOT NULL,
    `created` datetime NOT NULL,
    PRIMARY KEY (`catID`),
    KEY `parent` (`parent`)
    ) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;


    INSERT INTO `category` VALUES
    (29,'new mile',0,'2011-03-23 05:05:07'),
    (33,'sdfds',29,'2011-03-23 05:10:31'),
    (34,'2nd mile',0,'2011-03-23 05:48:18'),
    (35,'3rd mile',0,'2011-03-23 05:49:46'),
    (36,'3rd mile task',35,'2011-03-23 05:49:54'),
    (37,'4th mile',0,'2011-03-23 05:51:38'),
    (38,'4th task',37,'2011-03-23 05:51:44'),
    (39,'new new task!',29,'2011-03-23 08:05:11'),
    (40,'fdsf',29,'2011-03-23 08:11:25'),
    (41,'sdfsdf',29,'2011-03-23 08:12:00'),
    (42,'dff',29,'2011-03-23 08:12:52'),
    (43,'jhg',29,'2011-03-23 08:17:40'),
    (44,'tyu',29,'2011-03-23 08:17:56'),
    (45,'dsfdsf',29,'2011-03-23 08:18:07'),
    (46,'gfhfgh',34,'2011-03-23 08:18:25'),
    (47,'ok mile!',0,'2011-03-23 08:27:11'),
    (48,'okok task!',47,'2011-03-23 08:27:21');
    I Dunno LOL \(_o)/

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks -- being able to test makes all the difference
    Code:
    SELECT catID
         , name 
         , created AS parent_created
         , NULL    AS child_created
      FROM category
     WHERE parent = 0
    UNION ALL
    SELECT child.catID
         , child.name 
         , parent.created
         , child.created
      FROM category AS child
    INNER
      JOIN category AS parent
        ON parent.catID = child.parent
    ORDER
        BY parent_created
         , child_created
    
    catID  name           parent_created        child_created
      29   new mile       2011-03-23 05:05:07   NULL
      33   sdfds          2011-03-23 05:05:07   2011-03-23 05:10:31
      39   new new task!  2011-03-23 05:05:07   2011-03-23 08:05:11
      40   fdsf           2011-03-23 05:05:07   2011-03-23 08:11:25
      41   sdfsdf         2011-03-23 05:05:07   2011-03-23 08:12:00
      42   dff            2011-03-23 05:05:07   2011-03-23 08:12:52
      43   jhg            2011-03-23 05:05:07   2011-03-23 08:17:40
      44   tyu            2011-03-23 05:05:07   2011-03-23 08:17:56
      45   dsfdsf         2011-03-23 05:05:07   2011-03-23 08:18:07
      34   2nd mile       2011-03-23 05:48:18   NULL
      46   gfhfgh         2011-03-23 05:48:18   2011-03-23 08:18:25
      35   3rd mile       2011-03-23 05:49:46   NULL
      36   3rd mile task  2011-03-23 05:49:46   2011-03-23 05:49:54
      37   4th mile       2011-03-23 05:51:38   NULL
      38   4th task       2011-03-23 05:51:38   2011-03-23 05:51:44
      47   ok mile!       2011-03-23 08:27:11   NULL
      48   okok task!     2011-03-23 08:27:11   2011-03-23 08:27:21
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Very much thanks Rudy, cool sql!! It seems to work but if I added in something like this in the last line:

    ORDER BY parent_created DESC, child_created DESC

    the child will not be pulled. Parents are working cool with ordering DESC though. Also will there be an impact to the sql if in future I add in a "sorting" column and order everything by the sort column?

    Really appreciate your time and help Rudy.
    I Dunno LOL \(_o)/

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by justspree View Post
    ... the child will not be pulled.
    not sure what you mean by this

    the ORDER BY has no bearing on which rows are produced

    Quote Originally Posted by justspree View Post
    Also will there be an impact to the sql if in future I add in a "sorting" column and order everything by the sort column?
    if you just sort by some column, you can probably dispense with the UNION query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy! It all worked out now.
    I Dunno LOL \(_o)/


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
  •