How to order records that are chained together by status fields

Question

I have a table that holds a list of tasks to be performed by a process. Each task only works on items that match the input status, and when the task is completed it changes the item's status to the output status.

To track these tasks I use a table like this.

CREATE TABLE `tasks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(80) NOT NULL,
  `job_type` varchar(45) NOT NULL,
  `input_status` varchar(45) DEFAULT NULL,
  `output_status` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

The task's statuses form a chain of events. Here is an example

NULL -> NEW
NEW -> CREATE
CREATE -> INSPECT
INSPECT -> VERIFY
VERIFY -> PUBLISH

In real life the list of tasks is very long. note: The types of different statuses are unknown to me, these user defined values.

When I view the table using an order of input_status the records show the tasks in the wrong order. Sorting by input_status and output_status also doesn't work (obviously).

How can I sort the table where null is first, followed by the chain of input_status to output_status?

I figure that I'll have to create a virtual field to hold an extra sorting value, but I'm not sure what it should be or calculated.

Here is what I've tried so far, but it doesn't work.

SELECT *,
    (SELECT input_status FROM tasks AS parent 
        WHERE parent.output_status = tasks.input_status
    ) AS sorted
    FROM tasks
    ORDER BY sorted, input_status;

Oracle uses syntax called connect by prior. In concept your looking for a hierarchical query, which to my knowledge mySQL doesn't support. However there are approaches which may meet your need such as: http://stackoverflow.com/questions/8104187/mysql-hierarchical-queries or http://stackoverflow.com/questions/10646833/using-mysql-query-to-traverse-rows-to-make-a-recursive-tree but some of these are risky as they rely on undocumented behaviors to remain constant in upgrades. — xQbert, Aug 21 '13 at 13:11
There are two keyfield missing from this (detail) table definition; either a date or versionnumber + an object_id for which this table contains all the statusses. Also: most of the varchar fields don't look fully functional dependent on the PK, and should probably be put into the "master" table — wildplasser, Aug 21 '13 at 13:30
After some research I found this isn't possible with MySQL. Should I leave the question open or delete it? — Reactgular, Aug 21 '13 at 14:35

A Hocevar · Answer 1 · 2013-08-21T13:18:44.440

0

You can build a case statement on which you apply the sorting (not sure about the exact syntax however):

SELECT * FROM tasks 
    ORDER BY 
        CASE WHEN input_status IS NULL THEN '_' 
        ELSE output_status END ASC;

Of course you will have to adapt the case statement to your sorting needs.

edited Aug 21 '13 at 13:18

answered Aug 21 '13 at 13:13

A Hocevar

726
3
17

score 0 · Answer 2 · answered Aug 21 '13 at 13:49

I am not sure if you have defined a master relation on status values. If you had done it, query would have been easier.

Master Staus to hold status_id and status_text (null, new, verify, etc.). Child tasks to to refer from master for input and output status id values.

score 0 · Answer 3 · edited May 23 '17 at 11:54

Actually you need a reqursive query to do it. You can consider output_status as ID and input_status as a PARENT_ID. So you need to find full PARENT->CHILD path to assign an order number.

It can be done simple in MS SQL with recursive CTE but for MySQL as I know it isn't simple.

Try to use these UDF's to make sorting field for example [null->stat1->stat2->....]

Another approach if there is a fixed maximum status count you can do it using LEFT JOIN to connect previous records to find path to sort. Something like this (here are 3 levels of recursion):

SELECT tasks.*
    FROM tasks
    LEFT JOIN tasks t1 on t1.output_status=tasks.input_status
    LEFT JOIN tasks t2 on t2.output_status=t1.input_status
    LEFT JOIN tasks t3 on t3.output_status=t2.input_status

ORDER BY tasks.title,t3.output_status,t2.output_status,t1.output_status

Also here are links to consider:

Recursive self query

How to order records that are chained together by status fields

3 Answers3