CODEWITHSUNDEEP
mysqlsql
user3669901
user3669901

Reputation: 15

MySQL Order By Fields & Parents

So I have a table with 7 items. Lets call them: Car, Boat, House, Bike, Wheels, Sink, Bed. We can simply call the table Table

Each with the following assigned numbers:

(ID)  (Item)    (#)  (parent_id)
1234 - Car    -  1 -  null
0000 - Boat   -  2 -  null
2222 - House  -  4 -  null
6545 - Bike   -  5 -  null
6547 - Wheels -  0 -  1234
4442 - Bed    -  1 -  2222
1474 - Sink   -  0 -  2222

Wheels is a child of car (or bike, doesnt matter) and sink & bed are children of House. The parent information is stored in the column called parent_id. It is null for everything that doesn't have a parent.

I need to use MySQL to sort this table via the assigned number order (called ordinal, the # in my tables here), however, retaining the parent-child order information. So the list should be sorted as follows:

(ID)  (Item)    (#)  (parent_id)
1234 - Car    -  1 -  null
6547 - Wheels -  0 -  1234
0000 - Boat   -  2 -  null
2222 - House  -  4 -  null
1474 - Sink   -  0 -  2222
4442 - Bed    -  1 -  2222
6545 - Bike   -  5 -  null

How can I do this with mysql? We can assume all this information is on one table.

Upvotes: 0

Views: 263

Answers (3)

Alex
Alex

Reputation: 17289

You should better keep parent_id but not the parent name.

Here is quick solution to order your table http://sqlfiddle.com/#!9/2a1fb/3

SELECT *
FROM table1
ORDER BY 
   CASE WHEN parent_id IS NULL THEN CAST(ID AS CHAR)
   ELSE CONCAT(CAST(parent_id AS CHAR),'-', CAST(ID AS CHAR)) END

EDIT 1 Variant #2 :-) http://sqlfiddle.com/#!9/76dcb/23

SELECT t1.* 
FROM table1 t1
LEFT JOIN table1 t2
ON t2.ID = t1.parent_id
ORDER BY 
   CASE WHEN t2.ord_idx IS NULL THEN CAST(t1.ord_idx AS CHAR)
   ELSE CONCAT(CAST(t2.ord_idx AS CHAR),'-',CAST(t1.ord_idx AS CHAR)) END

EDIT 2 to see how this order works you can just add this field to select part like:

SELECT t1.*, CASE WHEN t2.ord_idx IS NULL THEN CAST(t1.ord_idx AS CHAR)
   ELSE CONCAT(CAST(t2.ord_idx AS CHAR),'-',CAST(t1.ord_idx AS CHAR)) END as my_order 
FROM table1 t1
LEFT JOIN table1 t2
ON t2.ID = t1.parent_id
ORDER BY 
   CASE WHEN t2.ord_idx IS NULL THEN CAST(t1.ord_idx AS CHAR)
   ELSE CONCAT(CAST(t2.ord_idx AS CHAR),'-',CAST(t1.ord_idx AS CHAR)) END

Upvotes: 1

Alexey
Alexey

Reputation: 3484

Assuming the following structure:

CREATE TABLE `Table` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`number` INT NOT NULL,
`parent_id` VARCHAR(32) DEFAULT NULL,
PRIMARY KEY(`id`)
);

something like this:

SELECT `Table`.* FROM `Table`
LEFT JOIN `Table` dependant ON dependant.`name` = `Table`.`parent_id`
ORDER BY CONCAT(IF(dependant.`name` IS NULL,`Table`.`name`,CONCAT(dependant.`name`,':',`Table`.`name`))) ;

may work.

NOTES

  1. When asking a question about RDBMS consider creating initial table structure and data SQL. There would be many more willing to answer then

  2. Read this article on how to store hierarchy data in MySQL. It used to host on .mysql.com, but for some reason they removed it. It's a wonderful arrticle.

  3. Consider moving to nested sets

Upvotes: 0

moo
moo

Reputation: 526

Something like that should work. I assumed the following field id, (#) = order_num, and parent_id:

SELECT * FROM table as t
ORDER BY
   CASE
       WHEN parent_id IS NOT NULL THEN (SELECT id FROM table WHERE id = t.parent_id LIMIT 1)
       ELSE id
   END,
   CASE
       WHEN parent_id IS NULL THEN -1
       ELSE order_num
   END;

Upvotes: 0

Related Questions