Получите иерархическое дерево из разных ссылочных таблиц с помощью SQL и PHP.

У меня есть несколько таблиц с аналогичной базовой структурой:

biopsy_p0
id | biopsy_id    | introduced

biopsy_p1
id | biopsy_p0_id | introduced

biopsy_p2
id | biopsy_p1_id | introduced

Моя цель - получить древовидное представление зависимостей между

biopsy_p0.id->biopsy_p1.biopsy_p0_id->biopsy_p2.biopsy_p1_id

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

--- Обновление: теперь у меня работает с PHP, это действительно не очень хорошее решение, и я надеялся, что смогу сделать это с SQL, чтобы его было немного лучше расширить:

Код PHP:

  $database = DatabaseFactory::getFactory()->getConnection();
      // Get all p0 element asociated with the biopsy
      $sql = "SELECT *
              FROM biopsy_p0
              WHERE biopsy_id = :id";
      $query = $database->prepare($sql);
      $query->execute(array(':id' => $id));
      $p0 = $query->fetchAll();

      // Get all p1 elements
      $sql="SELECT *
            FROM biopsy_p0 as p0
            RIGHT JOIN biopsy_p1 as p1
            ON p0.id=p1.biopsy_p0_id
            WHERE biopsy_id = :id;";

      $query = $database->prepare($sql);
      $query->execute(array(':id' => $id));
      $p1 = $query->fetchAll();

      for ($i=0; $i < count($p0); $i++)
      {
        $p1Array = new ArrayObject();
        foreach ($p1 as $key => $value)
        {
          if ($value->biopsy_p0_id == $p0[$i]->id)
          {
             $p1Array->append($value);
          }
          $p0[$i]->p1 = $p1Array;
        }
        unset($p1Array);
      }
      if ($p0 != NULL){
        return $p0;
      }
      return FALSE;

Результат: Это именно то, что мне нужно, но PHP беспорядочный, и его сложность увеличивается с каждым дочерним уровнем, который я хотел бы проверить.

 details:   Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [biopsy_id] => 226
            [introduced] => 2014-12-31
            [p1] => ArrayObject Object
                (
                    [storage:ArrayObject:private] => Array
                        (
                            [0] => stdClass Object
                                (
                                    [id] => 1
                                    [biopsy_id] => 226
                                    [introduced] => 2015-03-18
                                    [biopsy_p0_id] => 1
                                )

                            [1] => stdClass Object
                                (
                                    [id] => 3
                                    [biopsy_id] => 226
                                    [introduced] => 2015-03-17
                                    [biopsy_p0_id] => 1
                                )

                            [2] => stdClass Object
                                (
                                    [id] => 4
                                    [biopsy_id] => 226
                                    [introduced] => 2015-03-18
                                    [biopsy_p0_id] => 1
                                )

                        )

                )

        )

    [1] => stdClass Object
        (
            [id] => 2
            [biopsy_id] => 226
            [introduced] => 2014-12-31
            [p1] => ArrayObject Object
                (
                    [storage:ArrayObject:private] => Array
                        (
                            [0] => stdClass Object
                                (
                                    [id] => 2
                                    [biopsy_id] => 226
                                    [introduced] => 2015-03-31
                                    [biopsy_p0_id] => 2
                                )

                            [1] => stdClass Object
                                (
                                    [id] => 6
                                    [biopsy_id] => 226
                                    [introduced] => 2015-03-01
                                    [biopsy_p0_id] => 2
                                )

                        )

                )

        )

    [2] => stdClass Object
        (
            [id] => 3
            [biopsy_id] => 226
            [introduced] => 2014-12-31
            [p1] => ArrayObject Object
                (
                    [storage:ArrayObject:private] => Array
                        (
                            [0] => stdClass Object
                                (
                                    [id] => 5
                                    [biopsy_id] => 226
                                    [introduced] => 2015-03-11
                                    [biopsy_p0_id] => 3
                                )

                        )

                )

        )

)

Данные SQL:

CREATE TABLE IF NOT EXISTS `biopsy` (
  `id` int(11) unsigned NOT NULL,
  `creation_date` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=latin1;

INSERT INTO `biopsy` (`id`, `creation_date`) VALUES
(226, '2015-03-08'),
(227, '2015-03-08');

CREATE TABLE IF NOT EXISTS `biopsy_p0` (
`id` int(11) unsigned NOT NULL,
  `biopsy_id` int(11) unsigned NOT NULL,
  `introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `biopsy_p0` (`id`, `biopsy_id`, `introduced`) VALUES
(1, 226, '2014-12-31'),
(2, 226, '2014-12-31'),
(3, 226, '2014-12-31'),
(4, 227, '2015-03-14'),
(5, 255, '2015-03-10'),
(6, 255, '2015-03-12');

CREATE TABLE IF NOT EXISTS `biopsy_p1` (
`id` int(11) unsigned NOT NULL,
  `biopsy_p0_id` int(11) unsigned NOT NULL,
  `introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `biopsy_p1` (`id`, `biopsy_p0_id`, `introduced`) VALUES
(1, 1, '2015-03-18'),
(2, 2, '2015-03-31'),
(3, 1, '2015-03-17'),
(4, 1, '2015-03-18'),
(5, 3, '2015-03-11'),
(6, 2, '2015-03-01');

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


php sql hierarchical treeview
person wenzel    schedule 14.03.2015    source источник
comment
Измените RIGHT JOIN на LEFT JOIN   -  person Lennart    schedule 14.03.2015
comment
Спасибо за ваш ответ. Я уже пробовал это, но это также дает мне только концевые сноски. Так только - второй уровень А - второй уровень Б и так далее.   -  person wenzel    schedule 14.03.2015
comment
Можете ли вы предоставить образцы данных, иллюстрирующие проблему (создание таблицы и операторы вставки)?   -  person Lennart    schedule 14.03.2015
comment
Теперь я добавил PHP-решение проблемы. Но это действительно нехорошо, поэтому, если есть какие-либо идеи, как я мог бы справиться с этим с помощью SQL, я был бы очень признателен.   -  person wenzel    schedule 14.03.2015
comment
Смотрите мой предыдущий комментарий   -  person Lennart    schedule 15.03.2015

Ответы (1)


arrow_upward
0
arrow_downward

Начнем с объявления ключей:

CREATE TABLE IF NOT EXISTS `biopsy` (
  `id` int(11) unsigned NOT NULL primary key,
  `creation_date` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=latin1;

INSERT INTO `biopsy` (`id`, `creation_date`) VALUES
(226, '2015-03-08'),
(227, '2015-03-08');

CREATE TABLE IF NOT EXISTS `biopsy_p0` (
`id` int(11) unsigned NOT NULL primary key,
`biopsy_id` int(11) unsigned NOT NULL,
`introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

alter table biopsy_p0 add constraint fk_biopsy
    foreign key (biopsy_id)
    references biopsy (id)
        on update cascade
        on delete cascade;

INSERT INTO `biopsy_p0` (`id`, `biopsy_id`, `introduced`) VALUES
(1, 226, '2014-12-31'),
(2, 226, '2014-12-31'),
(3, 226, '2014-12-31'),
(4, 227, '2015-03-14');

-- violates the f.k. introduced
-- (5, 255, '2015-03-10'),
-- (6, 255, '2015-03-12');

CREATE TABLE IF NOT EXISTS `biopsy_p1` (
  `id` int(11) unsigned NOT NULL primary key,
  `biopsy_p0_id` int(11) unsigned NOT NULL,
  `introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

alter table biopsy_p1 add constraint fk_biopsy_p0
    foreign key (biopsy_p0_id)
    references biopsy_p0 (id)
        on update cascade
        on delete cascade;

INSERT INTO `biopsy_p1` (`id`, `biopsy_p0_id`, `introduced`)     
VALUES
(1, 1, '2015-03-18'),
(2, 2, '2015-03-31'),
(3, 1, '2015-03-17'),
(4, 1, '2015-03-18'),
(5, 3, '2015-03-11'),
(6, 2, '2015-03-01');

Я бы посоветовал вам называть вещи такими, какие они есть, то есть не называть идентификаторы столбцов идентификаторами и менять их имя в другом месте модели. Пример:

CREATE TABLE IF NOT EXISTS biopsy (
  biopsy_id int unsigned NOT NULL primary key,
  creation_date date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=latin1;

но я оставлю это в стороне. Теперь, когда мы знаем, что данные непротиворечивы:

select x.id as biopsy_id, x.creation_date
     , y.id as biopsy_p0_id, y.introduced as biopsy_p0_introduction
     , z.id as biopsy_p1_id, z.introduced as biopsy_p1_introduction 
from biopsy as x 
left join biopsy_p0 as y 
    on y.biopsy_id = x.id 
left join biopsy_p1 as z 
    on z.biopsy_p0_id = y.id 
order by x.id, y.id, z.id;

+-----------+---------------+--------------+------------------------+--------------+------------------------+
| biopsy_id | creation_date | biopsy_p0_id | biopsy_p0_introduction | biopsy_p1_id | biopsy_p1_introduction |
+-----------+---------------+--------------+------------------------+--------------+------------------------+
|       226 | 2015-03-08    |            1 | 2014-12-31             |            1 | 2015-03-18             |
|       226 | 2015-03-08    |            1 | 2014-12-31             |            3 | 2015-03-17             |
|       226 | 2015-03-08    |            1 | 2014-12-31             |            4 | 2015-03-18             |
|       226 | 2015-03-08    |            2 | 2014-12-31             |            2 | 2015-03-31             |
|       226 | 2015-03-08    |            2 | 2014-12-31             |            6 | 2015-03-01             |
|       226 | 2015-03-08    |            3 | 2014-12-31             |            5 | 2015-03-11             |
|       227 | 2015-03-08    |            4 | 2015-03-14             |         NULL | NULL                   |
+-----------+---------------+--------------+------------------------+--------------+------------------------+
7 rows in set (0.00 sec)

Остаётся чисто презентация и то лучше сделать на php.

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

Для большого количества уровней или если число неизвестно, вам нужна какая-то рекурсивная структура (обратите внимание, что вам также потребуются средства, чтобы задавать такие вопросы, большинство СУБД в наши дни имеют выражения рекурсивной общей таблицы, но MySQL нет. Вы можете решить некоторые вещи с помощью переменных, но довольно скоро это становится беспорядочным). У Troels Arvin есть коллекция ссылок по адресу:

http://troels.arvin.dk/db/rdbms/links/#hierarchical

что вы можете найти полезным.

person Lennart    schedule 15.03.2015
comment
Здравствуйте, Леннарт, большое спасибо за ваши усилия! Этот результат объединяет и форматирует данные иначе, чем я ожидал. Это определенно лучше расширить, чем мое решение. Также спасибо за ссылку, очень признательна. - person wenzel; 15.03.2015
comment
Рад, что это помогло, полезные термины для поиска по теме в SO и в других местах - это вложенные наборы, материализованный путь и транзитивное замыкание. - person Lennart; 15.03.2015