Laravel query builder recursive CTE support

builder.php

<?php
$recursive = $this->query()
    ->recursive('parents', function (Builder $query) {
        $query
            ->select([
                '*',
                new Alias('slug', 'fullslug'),
                new Alias(0, 'depth'),
                new Alias('id', 'tree_id'),
                new Alias('name', 'path'),
            ])
            ->from('categories_1')
            ->whereNull('parent_id')
            ->union(function (Builder $query) {
                $query
                    ->select([
                        'c.*',
                        (new Concat)
                            ->addColumn('fullslug')
                            ->addString('/')
                            ->addColumn('c.slug'),
                        new Expression('depth + 1'),
                        'tree_id',
                        (new Concat)
                            ->addColumn('path')
                            ->addString('&raquo ')
                            ->addColumn('c.name'),
                    ])
                    ->from((new MultiTable)
                           ->addTable(new Alias('categories_1', 'c'))
                           ->addTable(new Alias('parents', 'p'))
                    ->where('c.parent_id', '=', 'p.id');
            }, true);
    })
    ->select('*')
    ->from('parents')
    ->orderBy('tree_id')
    ->orderBy(new Coalesce('id', 'parent_id'))
    ->orderBy('depth')
    ->get();

query.sql

WITH RECURSIVE `parents` AS (
  (
    SELECT *, slug AS fullslug, 0 AS depth, id AS tree_id, name AS path 
    FROM `categories_1`
    WHERE `parent_id` IS NULL
  ) union all (
    SELECT `c`.*, CONCAT(fullslug,"/",c.slug), depth + 1, `tree_id`, CONCAT(path,"&raquo ",c.name)
    FROM categories_1 as c, parents as p WHERE `c`.`parent_id` = p.id
  )
) SELECT * FROM `parents` ORDER BY `tree_id` ASC, COALESCE(id, parent_id) ASC, `depth` ASC

Hi Ollie, what packages is this gist dependent on?

I'm looking to do exactly this with a hierarchy stored in mariadb 10.3

What package is this using?

@patrickomeara @notflip it was an old package of mine, super simple. It's essentially an extension of the builder, will dig it out when I get chance.

I have been stuck on a project with recursive mysql for 3 days now, it's killing me! I hope you can dig it out fast! Thanks very much

@notflip check out the work @staudenmeir has done with https://github.com/staudenmeir/laravel-cte/ and https://github.com/staudenmeir/laravel-adjacency-list

Might be useful for you.

Thanks Patrick, I already stumbled upon laravel-cte and it's working fine! Cheers