Fetch relations recursively avoiding n + 1 problem about laracon2018 HOT 6 CLOSED

@chelout That's awesome! Are you able to share a code example of that?

from laracon2018.

@reinink yeah, that's gonna be like this on mysql 8:

with recursive subordinates AS (
    select id, employee_id, name from employees where id = 1
    union all
    select employees.id, employees.employee_id, employees.name from employees
		inner join subordinates on subordinates.id = employees.employee_id
)
select * from subordinates;

from laracon2018.

@fdalcin have a look https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query

from laracon2018.

If you use mysql 8, or postgres, you can do it with recursive cte.

from laracon2018.

@chelout yeah, that would be the best solution.
Unfortunately MariaDB only supports that on 10.2 and I am not able to migrate at the moment.

Is there another way with a lower version?

Just one question about that solution, do I need to run the WITH RECURSIVE everytime? Or will it create some sort of table?

from laracon2018.

@chelout It seems to me that this approach will work as expected:

select id, employee_id, name
from (select * from employees order by employee_id, id) employee_list,
     (select @pv := 1) filter
where find_in_set(employee_id, @pv)
and length(@pv := concat(@pv, ',', id))

Thanks for your help

from laracon2018.