Good evening!
I am working on a package that is supposed to allow Laravel to have relationships based on MySQL Nested Set Model (lft and rgt keys).
Legend: X, Y, Z, A, B, C are integer numbers.
Let's assume that we want to use this with eshop categories.
My first task is to create a parent relation. I have managed to create a relation that finds a parent of a current category. My query looks like:
select * from categories where lft < X and rgt > Y order by lft limit 1
This works perfectly correct. But, the problem appears when I want to load, let's say, 100 categories. Then, it is one sql query for 100 categories:
select * from categories limit 100
and one sql query for a parent of each category:
select * from categories where lft < X and rgt > Y order by lft desc limit 1
That is 101 sql queries in total.
Here is where the problem part comes. I want to use a technique called Eager Loading (merging all relationship queries into one query). But how to do that?
Solution no. 1
My first solution was to collect all lft and rgt keys from:
select * from categories limit 100
and create query that looks like this:
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
but, this solution does not work at all. It returns all parents of the categories.
Solution no. 2
Then, I tried to make it work properly by using this method. An original query looks the same.
select * from categories limit 100
But loading the parents is quite different:
(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...
This query returns only relevant results, which is perfect, BUT to add a parent to its child, I have to run (on a PHP side) foreach cycle through all of the results of the original query (select * from categories limit 100) and inside that foreach I have to run another that iterates over every parent (from the original query) and inside the second foreach, there is a comparsion logic, that makes 10 000 (100 * 100) cycles plus the comparsion = looooooooooooong execution.
Solution no. 3
So I thought of another solution, which is the best in my opinion. It is just an improvement to the second solution.
Original query:
select * from categories limit 100
Relation query:
(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...
So, now, on the PHP side I have one array that contains results from the original query (100 items) and an array that contains results from the relation query (100 items). The improvement is that, now, every parent result contains lft and rgt key of an category that requested it (child_lft and child_rgt). Now, the PHP script is much faster. First I create a new array (let's name it $parents) that contains all parents, and every items key (key of value in $parents) is code (child_lft.child_rgt => 1.5) that identifies the category that requested it. This is a foreach that iterates 100 times. The second foreach iterates through the results from the original query and checks if $parents array contains a value with a key that suits its lft and rgt keys. So another 100 iterations. It total, 200 iterations = perfect ! But a 'relation query' is not as fast as I want it to be.
So, is there another way how to do that? Or is there a way how to make my sql query in the solution no. 3 faster?
Thank you just for reading it. Thanks!
You could just use the Baum which pretty much solves what you're trying to do in Laravel and has most of the corner cases covered.