Skip to content

Instantly share code, notes, and snippets.

@genakim
Forked from ollieread/builder.php
Created May 22, 2020 04:47
Show Gist options
  • Save genakim/bfe02cd06d0ac07d20e47e92b26c2069 to your computer and use it in GitHub Desktop.
Save genakim/bfe02cd06d0ac07d20e47e92b26c2069 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment