getting an adjacency model table selected into rows?

Spudhead · #1 (**permalink**) 03-02-10, 12:17

I've got the following table that should manage tasks and their sub-tasks:

Code:

CREATE TABLE `tasks` (
`idTask` int(10) unsigned NOT NULL auto_increment,
`idParentTask` int(10) unsigned default NULL,
`description` text NOT NULL,
`idActor` int(10) unsigned NOT NULL,
`requiredDate` datetime default NULL,
`priority` int(10) unsigned NOT NULL,
`completedDate` datetime default NULL,
`timeTaken` varchar(45) default NULL,
`createdDate` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY  (`idTask`)
)

Currently I can get a list of tasks from it via:

Code:

SELECT
	t0.idTask AS t0_idTask,
	t0.description AS t0_description,
	t1.idTask AS t1_idTask,
	t1.description AS t1_description,
FROM tasks t0
LEFT OUTER JOIN tasks t1
	ON t1.idParentTask = t0.idTask
WHERE t0.idParentTask IS NULL
ORDER BY t0.idTask, t1.idTask

I've got two problems with it, however... well, two symptoms of the same problem:
1. Sub-tasks are listed on the same row of the resultset as their parent task.
2. If I want to add another level to the query; "sub-sub-tasks", I've got to manually add more fields/joins to the query.

Is there a way to write an infinitely scaling version of this? One that'll output (for instance) a row for a "main" task, then a row for its sub-task, then a row for the sub-task of that sub-task, and then a row for the next main task, and so on ad infinitum? Or am I hopelessly wrong?

r937 · #2 (**permalink**) 03-02-10, 14:50

Quote:

Originally Posted by Spudhead

Is there a way to write an infinitely scaling version of this?

nope

what you could to is be practical and write as many LEFT OUTER JOINs as there are levels in your actual data

there are never an "infinite" number, there is always some maximum number of levels, and i have seen tests where 15 joins are handled efficiently, so don't worry about that part of it

as for sorting, you don't actually have to put the subtasks and subsubtasks on the same line with their parents, you can show one task per line, but you do need the joins in order to achieve the proper sorting

give me some sample data for three levels and i'll show you how

mike_bike_kite · #3 (**permalink**) 03-02-10, 15:21

Couldn't you just call a stored procedure to loop round grabbing each sub level of data and storing the set of task ids in a work table. You'd then just make all your joins against the work table. That way you can encapsulate all the multiple level stuff into one stored proc and the rest of the SQL code you write would be pretty simple. Another smaller benefit would be that it is infinitely scaling.

r937 · #4 (**permalink**) 03-02-10, 15:54

Quote:

Originally Posted by mike_bike_kite

Another smaller benefit would be that it is infinitely scaling.

given the assumptions that you have infinite disk space for the temp table and infinite computing resources (as well as infinite time to wait for the results), i would have to agree

demods · #5 (**permalink**) 03-02-10, 16:27

Quote:

Originally Posted by Spudhead

Is there a way to write an infinitely scaling version of this? One that'll output (for instance) a row for a "main" task, then a row for its sub-task, then a row for the sub-task of that sub-task, and then a row for the next main task, and so on ad infinitum? Or am I hopelessly wrong?

Maybe this article would have helped you --> MySQL :: Managing Hierarchical Data in MySQL

r937 · #6 (**permalink**) 03-02-10, 19:55

maybe this article will help, too -- Categories and Subcategories

Internet Services

Web Development

Digital Marketing

Consumer Tech