Can I define the sort order of siblings with an order column

[groenewege]

Hi everyone,

I would like to use this package to organise the hierarchy of elements with parent_id and an order_column for the siblings.
Is this possible ?

Given the following records :

id	title	parent_id	order_column
1	Page 1	null	1
2	Page 1.1	1	1
3	Page 1.3	1	3
4	Page 1.2	1	2
5	Page 1.1.1	2	1

I would like to order the pages by depth and order_column :

• Page 1
  • Page 1.1
    • Page 1.1.1
  • Page 1.2
  • Page 1.3

If I get the tree of the records and use the breadthFirst method the Page 1.1.1 is displayed at the end and not after Page 1.1 because the records are first ordered by depth.

Page::tree()->breadthFirst()->orderBy('order_column')

If I get the tree of the records and use the depthFirst method the Page 1.3 is displayed before Page 1.2 because the records are first ordered by the path that uses the parent_id and the id columns.
Adding a second ordering command like orderBy('order_column') can not change the order by path.

Page::tree()->depthFirst()->orderBy('order_column')

Is it possible to use adjacency list with an order column to define the order between siblings instead of this order only being decided by the parent id and the primary key ?

[staudenmeir]

Hi @groenewege,
Try adding a custom path for order_column and using it to order depth-first:

class Page extends Model
{
    use HasRecursiveRelationships;

    public function getCustomPaths()
    {
        return [
            [
                'name' => 'order_path',
                'column' => 'order_column',
                'separator' => '.',
            ],
        ];
    }
}

Page::tree()->orderBy('order_path')->get();

[groenewege]

Thank you ! this works.

[groenewege]

I discovered that there is an issue once the order_column has multiple digits. This orders 11 before 2.
I used the raw query from your Mysql Grammar file to fix this issue.
->orderByRaw("regexp_replace( regexp_replace( order_path, '(^|[.])(\\\\d+)', '$100000000000000000000$2' ), '0+(\\\\d\{20\})([.]|$)', '$1$2' ) asc");

[groenewege]

There is another solution by using the LPAD MySQL function in the custom path definition :

use Illuminate\Support\Facades\DB;

public function getCustomPaths(): array
{
    return [
        [
            'name' => 'order_path',
            'column' => DB::raw('LPAD(pages.order_column, 5, 0)'),
            'separator' => '.',
        ],
    ];
}