Skip to content

Instantly share code, notes, and snippets.

@ollieread
Last active February 15, 2022 15:48
Show Gist options
  • Save ollieread/e090c6c9d07a2214383d8cbba928043c to your computer and use it in GitHub Desktop.
Save ollieread/e090c6c9d07a2214383d8cbba928043c to your computer and use it in GitHub Desktop.
Laravel query builder recursive CTE support
<?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();
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
@notflip
Copy link

notflip commented Jul 30, 2019

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

@patrickomeara
Copy link

@notflip
Copy link

notflip commented Aug 2, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment