Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум программистов > MySQL > Иерархический подсчет элементов


Автор: zheka1980 20.9.2014, 23:04
Господа, есть у меня иерархический каталог ссылок.

То есть одна таблица в которой записи типа:
запись 1:  id=1  caption="ул.Иванова" parent="г. Мухосранск"
запись 2:  id=2  caption="дом 1"          parent="1"
запись 3:  id=3  caption="дом 2"          parent="1"
запись 4:  id=4  caption="кв.4"            parent="3"

Структура (иерархия) строится по взаимосвязям id и parent, то есть, если я хочу узнать список домов на улице Иванова, я ищу все записи у которых id=1, если меня интересуют номера квартир в доме 2, я ищу все записи у которых id=3 и так далее.


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

Как мне одним запросом в MySQL получить все квартиры на улице Иванова?

Автор: ТоляМБА 21.9.2014, 13:35
Цитата(zheka1980 @  21.9.2014,  01:04 Найти цитируемый пост)
Теперь вопрос - я хочу получить список всех квартир на улице Иванова.
Будет у вас список из 100 квартир № 1, что будете с ним делать?

Добавлено через 4 минуты и 38 секунд
Вот вместе с домами:

Код
Select T2.caption, C.caption
from Table_2 T2
inner join
(Select T1.id, T1.caption, T1.parent
from Table_2 T1
inner join
(select T.id
from Table_2 T
inner join
(select id
from table_2
where caption='ул.Иванова' and parent='г. Мухосранск') as A
on t.parent=a.id) as B
on T1.parent=B.id) as C
on T2.id=C.parent
order by 1, 2

Автор: zheka1980 21.9.2014, 14:53
Цитата

Будет у вас список из 100 квартир № 1, что будете с ним делать?

1. считать, сколько всего квартир.
2. выбирать случайную квартиру, пофиг из какого дома, главное чтобы была с улицы Ивнова.

Автор: ТоляМБА 21.9.2014, 15:00
zheka1980, так вам мой вариант с выводом квартир и домов подходит или оставить только квартиры?

Автор: zheka1980 21.9.2014, 20:07
Пока не испытывал. НИчего убирать не надо, попробую сам разобраться.
Спасибо за помощь.

Автор: zheka1980 21.9.2014, 20:23
Уточню задачу. Видимо без вашей помощи не обойдусь.
Дело в том, что квартиры у меня в отдельной таблице "flats", в этой таблице есть автоинкрементное поле id, и parent, привязывающий к записи номера дома в таблице town (города и улицы находятся в таблице "town")


В общем задачи две, можно, и даже нужно каждую из них решить отдельным запросом.
1. На входе имеем id улицы Иванова, то есть 1, это будет входным параметром, вычисляемым в php скрипте, и передаваемым в функцию.
Нужно посчитать сколько в таблице flats имеется записей, у которых parent привязан к тому дому в таблице "town", который в свою очередь привязан к улице Иванова. Но оперировать надо не caption="ул.Иванова", а id, который, как уже сказано, равен 1
2. То же самое, но получить нужно массив из 10 первых записей таблицы "flats", которые я буду сортировать по параметрам. Ну грубо говоря, запрос должен оканчиваться на "ORDER by rank LIMIT 10"

Спасибо.

Автор: Akina 22.9.2014, 07:48
Для эффективного решения такого рода задач необходимо перейти на иной способ хранения. Например, на nested set.
Либо пойти на переопределение данных - например, хранить материализованный путь узла (полный адрес).

Автор: zheka1980 22.9.2014, 18:43
Нашел статью вроде бы о том, что мне нужно - http://club.shelek.ru/viewart.php?id=307

Там написано - создать таблицу с такими-то параметрами. Я создал.
Дальше предлагается сделать это
Код

CREATE PROCEDURE fetch_subtree_ids(IN base INT UNSIGNED)
BEGIN
    DECLARE ids TEXT DEFAULT '';

    SET @parents = base;
    SET ids = base;

    loop1: LOOP
        SET @stm = CONCAT(
            'SELECT GROUP_CONCAT(id) INTO @parents FROM test1',
            ' WHERE parent_id IN (', @parents, ')'
        );

        PREPARE fetch_childs FROM @stm;
        EXECUTE fetch_childs;
        DROP PREPARE fetch_childs;

        IF @parents IS NULL THEN LEAVE loop1; END IF;

        SET ids = CONCAT(ids, ',', @parents);
    END LOOP;

    SET @stm = CONCAT('SELECT id FROM test1 WHERE id IN (', ids, ')');

    PREPARE fetch_childs FROM @stm;
    EXECUTE fetch_childs;
    DROP PREPARE fetch_childs;
END;




Я не понял - это как, целиком скормить MySQL ? Мой сервер подавился этой строкой.

Автор: zheka1980 22.9.2014, 22:19
Эх... пока ждал ответа и, почитывая статейки, тихо ужасался nested set, решил все-таки попробовать подсчитывать рекурсивной функцией на PHP.
То есть, перебираю всех потомков до самого дна (то есть до первого "бездетного" потомка), попутно составляя список этих самых бездетных потомков. 
Получилось около 400 запросов
Далее склеиваю строку типа
Код

SELECT * FROM flats WHERE parent IN('385','386','387','388',............... ВСЕГО около 400 элементов )


Забил таблицу flats мусором из 1500 записей. И выполнил в ней поиск вышеуказанной строкой.
Заняло 0.0042 сек. (Комп - Core2Dou 3 ГГц)



Ответьте на последний вопрос и я от вас отстану: 
В идеале планируется 100 000 записей, элементов каталога 400, вложенность - 3-4, не более, скажите, сканирование 400 запросами  и одним, 401-м, но длиннющим запросом выборка - это будет насилием над MySQL или если работает, то нормально?

Автор: Akina 23.9.2014, 08:12
Цитата(zheka1980 @  22.9.2014,  23:19 Найти цитируемый пост)
решил все-таки попробовать подсчитывать рекурсивной функцией на PHP.

Организуй то же на стороне сервера на курсорах - сэкономишь на трафике и уж точно не проиграешь по скорости.
Цитата(zheka1980 @  22.9.2014,  23:19 Найти цитируемый пост)
Получилось около 400 запросов Далее склеиваю строку типа

Смотри не вылети за размер пакета. Будет не очень приятно...
Цитата(zheka1980 @  22.9.2014,  23:19 Найти цитируемый пост)
тихо ужасался nested set

Это ты просто не "въехал" в их идеологию. Ренально там всё достаточно просто. Тем более что запросы шаблонные, и их типов немного, шаблоны можно параметризовать и захардкодить - или вообще выволочь на сервере в хранимки.

Автор: =Женек= 23.9.2014, 08:29
Цитата

Это ты просто не "въехал" в их идеологию. 

Можете на пальцах объяснить практический смысл left key и right key?
Исходя из чего они формируются и самое главное, что они дают?

Цитата

Смотри не вылети за размер пакета. Будет не очень приятно...


А каков он в среднем?

Добавлено @ 08:31
Цитата

Организуй то же на стороне сервера на курсорах - сэкономишь на трафике и уж точно не проиграешь по скорости.


Секунду.. PHP и MySQL ведь на одном сервере. О каком трафике идет речь?
И что такое курсоры?

Автор: zheka1980 23.9.2014, 20:48
В смысл Nested Set въехал. По сути это та же рекурсия, мой скрипт проходит по дереву каталога тот же путь, что и leftkey и rightkey. 
Только у моего скрипта это как-то само получается, а тут все разжевывать надо.
Пытаюсь понять, насколько MySQL будет быстрее с этим справляться.

Хм... а ведь в Nested Sets это будет даже проще и точно быстрее. Нужно просто выбрать записи, где разница между leftkey и rightkey не больше единицы - это и будут концевые, "бездетные" узлы. 
Но этой процедурой мы лишь избавляем MySQL от 400 запросов.
Но еще нужно выбрать в таблице №2 все элементы, принадлежащие одному из 400 идентификаторов, полученных в предыдущем запросе.

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)