The best way to retrieve Parent/Child Association?

Databases
1

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:


id  | name       | parent
1   |  cat1        |  0
2   |  sub-cat2  |  1
3   |  sub-cat3  |  1
4   |  cat4        |  0

End result preferably can be easily loop from programming layer like e.g:


array( cat1 => array( sub-cat2, sub-cat3 ),
          cat4 => array() )

Thanks for any help.

2

SELECT * FROM daTable
ORDER BY COALESCE(NULLIF(parent,0),id),id

:cool:

3

wooal this is something I haven’t seen before! :eek:
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!

4

you have a copy of the mysql manual, right?

ORDER BY created DESC

:slight_smile:

5

I tried but doesn’t seems to order correctly:

SELECT * FROM daTable
ORDER BY COALESCE(NULLIF(parent,0),id),id, created DESC

6

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

7

erm so is:

SELECT * FROM daTable
ORDER BY COALESCE(NULLIF(parent,0),id),created desc

?Sorry I’m dumb :frowning:

8

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?

9

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”

10

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’);

11

thanks – being able to test makes all the difference :slight_smile:

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
12

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.

13

not sure what you mean by this

the ORDER BY has no bearing on which rows are produced

if you just sort by some column, you can probably dispense with the UNION query

14

Thanks Rudy! It all worked out now.