扁平嵌套关系按父母和儿童排序 - 可能只有雄辩？

Question

Is this possible using eloquent alone:

// build doc tree
public function tree()
{
    $docs = app(config('lap.model.doc'))->with(['children.parent', 'children' => function ($query) {
        return $query->orderBy('order');
    }])->whereNull('parent_id')->orderBy('order')->get()->toArray();

    $docs_array = [];

    foreach ($docs as $doc)
    {
        $docs_array[] = array_except($doc, 'children');

        if ($doc['children']) {
            foreach ($doc['children'] as $child) {
                $docs_array[] = $child;
            }
        }
    }

    return $docs_array;
}

Just curious because despite this working the way I want it to, I'm wondering if there is a better solution that might already exist within the framework capacity.

Answer 1

Something like this can be used in SQL:

select t.*
from t
order by coalesce(t.parent_id, t.id), `order`
;

i.e. The coalesce function uses id when there is no parent_id. This "clusters" the familiy of parent and children together and then rows within the family can make use of the "order" column.

Whilst I'm inexpert at Laravel I believe orderByRaw may be used, like so:

orderByRaw('coalesce(parent_id, id), `order`')

Also see this answer

If using a coalesce function is a problem, this alternative can be used in SQL:

select t.*
from t
order by case when t.parent_id IS NULL THEN t.id else t.parent_id end, t.`order`
;

and that case expression could be converted into an IF for use by Laravel I suspect.