Recursive MySQL query to parents and siblings

Question

Recursive MySQL query to parents and siblings

Navigation

#1 by (2 votes)

0

The idea is to launch a single query which when you insert or modify a task, either the parent task (that your parent_task_id points to null) or a child task (that your parent_task_id points to the id of your parent task) updates the hours field of the modified task and adds up to the project, including the siblings of the parent tasks, also avoiding the collision of another query itself, the latter with a transaction I do not know if it would work correctly in this case.

I have the following tables:

PROJECTS (summary)

id integer auto_increment primary key
name varchar (250) not null
hours float not null default 0

TASKS (summary)

id integer auto_increment primary key
parent_task_id integer null
project_id integer not null
name varchar (250) not null
description text null
hours float not null default 0

For more laps that I have given in PHP rescuing the id of the parent task and adding in a loop, which I do not see it correct to attack the database so much with something as usual, as it is possible to launch the same query in a reduced time interval.

mysql

asked by dddenis 30.12.2016 в 13:49

source

1 answer

transform number to date Sort alphabetically two columns, PostgreSQL

score 2 · Accepted Answer

With a recursive procedure in SQL you can get the number of hours updated in all the TASKS and in the PROJECT:

CREATE DEFINER='admin'@'%' PROCEDURE 'SP_AddTaskTime'(
IN 'para_task_id' INT,
IN 'para_hours' FLOAT)

LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

-- Update task actual con las nuevas horas
UPDATE TASKS SET hours = hours + para_hours WHERE id = para_task_id;

-- Preparar la siguiente iteracion de recursividad
SET @PARENT_TASK_ID = (SELECT t.parent_task_id FROM TASKS t WHERE t.id = para_task_id);
SET @PROJECT_TASK_ID = (SELECT t.project_id FROM TASKS t WHERE t.id = para_task_id);

-- No hay parent_task: actualizar horas de proyecto y acabar
IF (@PARENT_TASK_ID IS NULL) THEN
    -- Update project
    UPDATE PROJECTS SET hours = (SELECT SUM(t.hours) FROM TASKS t 
                         WHERE t.project_id = @PROJECT_TASK_ID 
                         AND t.parent_task_id IS NULL) 
    WHERE id = @PROJECT_TASK_ID;
-- Existe un parent_task: continuar recursion con el parent
ELSE
    -- Llamada recursiva
    CALL SP_AddTaskTime(@PARENT_TASK_ID, para_hours);
END IF;

END

The function has 2 arguments:

para_task_id INT - Id of the current task. In each call of the recursion, this parameter will change its value.
para_hours FLOAT -Number of hours to add. This parameter remains constant.

The first thing the function does is add the hours to the current task id.

In the next step, the value of the parent and project task is read:

If the parent tare is NULL , there are no more tasks than adding the hours, so I have reached the end of the recursion and only the project's hours are updated.
If the parent task is not NULL , I follow the recursion with the task id of my father.

An example of a procedure call would be:

CALL SP_AddTaskTime(3, 0.5);

This call would add 0.5 hours to the TASKS with id = 3 and would update all parent tasks and the number of total hours of PROJECTS .

By the way, you will surely have to change the value of the SQL variable max_sp_recursion_depth . This variable indicates the number of recursive calls that are allowed. If you have a maximum depth of 10 tasks, this value must be updated as well:

SET max_sp_recursion_depth = 10