Модель вложенного множества MySQL с отношениями Laravel

Добрый вечер!

Я работаю над пакетом, который должен позволить Laravel иметь отношения, основанные на модели вложенного набора MySQL (ключи lft и rgt).

Обозначения: X, Y, Z, A, B, C — целые числа.

Давайте предположим, что мы хотим использовать это с категориями eshop.

Моя первая задача — создать родитель связь. Мне удалось создать отношение, которое находит родителя текущей категории. Мой запрос выглядит так:

select * from categories where lft < X and rgt > Y order by lft limit 1

Это работает совершенно правильно. Но проблема возникает, когда я хочу загрузить, скажем, 100 категорий. Тогда это один SQL-запрос для 100 категорий:

select * from categories limit 100

и один SQL-запрос для родителя каждой категории:

select * from categories where lft < X and rgt > Y order by lft desc limit 1

То есть 101 Всего запросов sql.

Здесь начинается проблемная часть. Я хочу использовать технику под названием Eager Loading (объединение всех запросов отношений в один запрос). Но как это сделать?

Решение № 1

Моим первым решением было собрать все ключи lft и rgt из:

select * from categories limit 100

и создайте запрос, который выглядит так:

select * from categories where (lft < X or lft < Y or lft < Z ...)
and ( rgt > A or rgt > B or rgt > C ...) order by lft desc

но это решение не работает вообще. Возвращает всех родителей категорий.

Решение № 2

Затем я попытался заставить его работать правильно, используя этот метод. Исходный запрос выглядит так же.

select * from categories limit 100

Но загрузка родителей совсем другая:

(select * from categories where lft < X and rgt > Y order by lft desc limit 1)
union all
(select * from categories where lft < A and rgt > B order by lft desc limit 1)
union all...

Этот запрос возвращает только релевантные результаты, что идеально, НО, чтобы добавить родителя к своему дочернему элементу, я должен выполнить (на стороне PHP) цикл foreach для всех результатов исходного запроса (выберите * из предела категории 100) и внутри этого foreach мне нужно запустить другой, который выполняет итерацию для каждого родителя (из исходного запроса), а внутри второго foreach есть логика сравнения, которая составляет 10 000 (100 * 100) циклов плюс выполнение сравнения = looooooooooooong.

Решение № 3

Поэтому я подумал о другом решении, которое является лучшим на мой взгляд. Это просто улучшение второго решения.

Исходный запрос:

select * from categories limit 100

Запрос отношения:

(select categories.*, X as child_lft, Y as child_rgt from categories
where lft < X and rgt > Y order by lft desc limit 1)
union all
(select categories.*, A as child_lft, B as child_rgt from categories
where lft < A and rgt > B order by lft desc limit 1)
union all...

Итак, теперь на стороне PHP у меня есть один массив, который содержит результаты исходного запроса (100 элементов) и массив, который содержит результаты запроса отношения (100 элементов). Улучшение в том, что теперь каждый родительский результат содержит ключи lft и rgt категории, которая его запросила (child_lft и child_rgt). Теперь скрипт PHP намного быстрее. Сначала я создаю новый массив (назовем его $ parent), который содержит всех родителей, а ключ каждого элемента (ключ значения в $ parent) — это код (child_lft.child_rgt => 1.5), который идентифицирует категорию, которая его запросила. Это foreach, который повторяется 100 раз. Второй foreach просматривает результаты исходного запроса и проверяет, содержит ли массив $ parent значение с ключом, который соответствует его ключам lft и rgt. Итак, еще 100 итераций. Итого, 200 итераций = отлично! Но «запрос отношения» не так быстр, как я хочу.

Итак, есть ли другой способ, как это сделать? Или есть способ, как сделать мой SQL-запрос в решении нет. 3 быстрее?

Спасибо, что прочитали это. Спасибо!