PHP + MySQL: поиск общего количества значений путем рекурсии по родительским дочерним группам mySQL

У меня есть таблица MySQL, как это:

Папки

[id] [name] [parent_id]
1    fruits  0
2    orange  1
3    lemon   2
4    steak   0

Проекты

[id] [name]     [parent_id]   [hours]
1    project1    1            3
2    project2    2            4
3    project3    3            6
4    project4    4            7

В основном проекты могут находиться в разных папках. parent_id проектов - это идентификатор папки. Папки могут иметь вложенные папки, а проекты могут находиться в подпапках. И есть неограниченное количество подпапок.

Результат, который я хотел бы получить, - это найти общее количество часов в папке и во всех ее подпапках. SO в основном проходит через папку и находит все дочерние папки в этой папке (и продолжает делать это, пока не достигнет самого глубокого уровня) и добавляет все часы для всех проектов в каждой из этих подпапок, чтобы получить общее количество часов.

Думайте об этом как о базе данных задач или проектов, и я хочу рассчитать общее количество часов, потраченных на проект.

Есть ли способ сделать это через mySQL или PHP?

Пока у меня есть следующая информация для mySQL. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

И у меня есть эта функция PHP, но проблема в том, что я не знаю, как добавить итог из результатов. Я не знаю, как добавить массив в рекурсивную функцию. Это, вероятно, неправильно в любом случае, но я подумал, что просто выброшу это там.

function categoryChild($id) {
    $s = "SELECT id FROM folders WHERE parent_id = $id";
    $r = mysql_query($s);
    $children = array();
    $hours = array();
    if(mysql_num_rows($r) > 0) {
        # It has children, let's get them.
        while($row = mysql_fetch_array($r)) {
            # Add the child to the list of children, and get its subchildren
            $ee['id'] = categoryChild($row['id']);
            $newid = $row['id'];
            $sql = "SELECT SUM(b.hours) as totalhours
            FROM folders a
            INNER JOIN projects b ON b.folder_id = a.id
            WHERE a.id = '$newid'";
            $result = mysql_query($sql);
            $children['hours'] = $row['totalhours'];

            }
        } 
        $s1 = "SELECT sum(hours) as totalhours FROM projects WHERE folder_id = $id";
        $r1 = mysql_query($s1);
        if(mysql_num_rows($r1) > 0) {
            while($row3 = mysql_fetch_array($r1)) {
            $children['hours'] = $row3['totalhours'];
            }
        }

    $data = $data['hours'];
    return $data;
}

mysql php recursion totals
person Scott Yu - builds stuff    schedule 01.11.2010    source источник
comment
Вы исследовали использование вложенных наборов, как описано в ссылке mysql, которая у вас есть? Модель вложенных наборов хорошо подходит, если у вас есть приложение, интенсивное чтение, но теряет свою эффективность, если вам приходится выполнять много операций записи по сравнению с чтением.   -  person rojoca    schedule 01.11.2010
comment
Я бы предпочел использовать закрывающую таблицу. См. слайды Билла Карвина, они включают пример глубокого древовидного поиска для модель смежности и возможные альтернативы для вашей текущей установки.   -  person Wrikken    schedule 01.11.2010

Ответы (2)


arrow_upward
1
arrow_downward

Это было сложно... но я думаю, что наконец решил это.

$totalhours = getSubfolders($id);
$totalhours = findTotalHours($totalhours,$id);


function findTotalHours($array,$id) {
 global $db, $user_id;
 $array = array_keys_multi($array);
 $array[] = $id; // i wanted to get all projects under starting folder
 $totalhours = implode(',',$array);
 $sql = "SELECT SUM(hours) AS totalhours FROM projects WHERE folder_id IN ($totalhours)";
 $row = $db->query_first($sql); 
 return $row['totalhours'];
}

function getSubfolders ($id) { //http://stackoverflow.com/questions/2398402/recursive-function-to-get-all-the-child-categories
    $s = "SELECT id FROM folders WHERE parent_id = $id";
    $r = mysql_query($s);
    $children = array();
    if(mysql_num_rows($r) > 0) {
        # It has children, let's get them.
        while($row = mysql_fetch_array($r)) {
            # Add the child to the list of children, and get its subchildren
            $children[$row['id']] = getSubfolders($row['id']);
        }
    }
    return $children;
}

function array_keys_multi(array $array) //http://codeaid.net/php/extract-all-keys-from-a-multidimensional-array
{
 $keys = array();
 foreach ($array as $key => $value) {
  $keys[] = $key;
  if (is_array($array[$key])) {
   $keys = array_merge($keys, array_keys_multi($array[$key]));
  }
 }
 return $keys;
}
person Scott Yu - builds stuff    schedule 03.11.2010

arrow_upward
0
arrow_downward

Я не уверен, что понимаю, что вы пытаетесь сделать с вашим кодом, но вот схема того, как я бы это организовал.

function getTotalHours( $id) {

  $sum = getHoursOnProject( $id);
  foreach( getChildIds( $id) as $childId) {
    $sum += getTotalHours( $childId);
  }
  return $sum;
}

function getChildIds( $id) {
  $query = "SELECT id FROM folders WHERE parent_id = $id";
  ...
  return $childIdArray;
}

function getHoursOnProject( $id) {
  $query = "SELECT sum(hours) as totalhours FROM projects WHERE folder_id = $id";
  ...
  return $hours;
}
person Jon Snyder    schedule 01.11.2010