Закрытие стола лучший SELECT QUERY

Question

Я занимаюсь разработкой системы многопоточных комментариев для MySQL и PHP. Я выбрал шаблон Closure Table, но у меня проблема. Мне нужен запрос (запросы), чтобы получить все дерево. Как это сделать? Я много об этом искал, но не могу найти ничего оптимального. Если у вас есть что-то лучше для комментариев, пожалуйста, дайте мне знать. Спасибо за ваш ответ.

Answer 1

Это то, что я до сих пор:

SELECT `Comments`.* FROM `Comments`
LEFT JOIN `TreePaths` ON `Comments`.`iD` = `TreePaths`.`descendant`
WHERE `TreePaths`.`ancestor` = <Root ID>

Это вытянет все узлы для данного дерева из таблицы закрытия, но в данный момент они не упорядочены правильно. Я обновлю этот пост, если когда-нибудь выясню последнюю часть. Программно вы можете получить правильный порядок с информацией в наборе результатов, но я бы предпочел, чтобы это было правильно в результатах.

Answer 2

Если этот вариант использования является распространенным в вашем приложении, просто сохраните корневой идентификатор (идентификатор корневого элемента дерева. Это может быть идентификатор сообщения, которому принадлежат эти комментарии). Теперь, когда вам нужно получить все дерево комментариев, вам просто нужно сделать:

SELECT * FROM comments WHERE root_id = <root_id>

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

Обновить:

$dbh = new PDO($dsn, $user, $password);
$sql = "SELECT A.*, GROUP_CONCAT(descendant) as descendants FROM Comments AS A INNER JOIN Paths AS B ON A.id = B.ancestor WHERE A.item = ? GROUP BY A.id";
$stmt = $dbh->prepare($sql);
$stmt->execute(array($item));
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

$adjacency_list = array(); $comments = array();
foreach($data as $row) {
    $comments[$row['id']] = $row;
    $descendants = explode(',', $row['descendants']);
    $adjacency_list[$row['id']] = $descendants;
}

echo '<UL>';
foreach($adjacency_list[$item] as $top_level_comment) {
    printTree($top_level_comment, $adjacency_list[$top_level_comment]);
}
echo '</UL>';

function printTree($node, $descendants) {
    echo '<LI>'.$node;
    if(sizeof($descendants) > 0) {
        echo '<UL>';
        foreach($descendants as $descendant) {
            $d = array();
            if(!empty($adjacency_list[$descendant])) $d = $adjacency_list[$descendant];
            printTree($descendant, $adjacency_list[$descendant]);
        }
        echo '</UL>';
    }
    echo '</LI>';
}

Модель Nested Set Model, с другой стороны, не является хорошим решением для комментирования системы из-за большого количества вставок и обновлений. Это эффективное решение, если ваши данные редко обновляются.

Answer 3

Я наткнулся на ваш вопрос, потому что я смотрю и на таблицы закрытия. Я думаю, что ваша проблема может быть решена, если вы последуете совету Билла Карвина по поводу таблиц закрытия в его книге "Sql anti-pattern":

Сохраните одну строку в этой таблице для каждой пары узлов в дереве, которые имеют общие отношения предок / потомок, даже если они разделены несколькими уровнями в дереве.

(мой акцент)

Таким образом, один запрос на parentId будет производить все дети родителя в одном кадре. (В SQL-сервере это может быть сделано с помощью рекурсивного запроса).

А также:

Вы можете улучшить таблицу закрытия, чтобы упростить запросы для непосредственных родительских или дочерних узлов. Добавьте атрибут TreePaths.path_length в дизайн таблицы закрытия.

То есть: сохранить расстояние между элементами в таблице закрытия.

Я думаю, вам действительно понравится его пример. Это касается также комментариев.