Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: getting an adjacency model table selected into rows?

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Spudhead View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2009
    Posts
    23
    Quote Originally Posted by Spudhead View Post
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maybe this article will help, too -- Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •