How to order by child / parent column with Eloquent?

Question

I have a table called terms which contains the following fields:

id | name | slug | taxonomy | parent_id

This is the current datasource:

id | name | slug | taxonomy | parent_id
 1   Pop    pop    category   null
 2   Rock   rock   category   null
 3   Jazz   jazz   category   2
 4   Edm    edm    category   3

Essentially I want return as result something like this:

Pop
Rock
— Jazz
— — Edm

Because Jazz and Edm are childs of Rock, so I wrote this:

return Term::where([
       'taxonomy' =>  'category'
   ])->orderBy('parent_id', 'ASC');

The problem's that when I order the Datatable by a specific column I doesn't get the desired order, and Eloquent will return the records ordered by the column selected eg:

column: name
order: desc

result:

Rock
Pop
— Jazz
— — Edm

Expected result:

Rock
— Jazz
— — Edm
Pop

Term Model

<?php

namespace App\Cms\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Factories\HasFactory;

class Term extends Model
{
    use HasFactory, Sluggable;

    protected $table = 'terms';
    protected $fillable = [
        'name',
        'slug',
        'taxonomy',
        'description',
        'parent_id',
        'term_group',
    ];

    public function parent()
    {
        return $this->hasOne(Term::class, 'id', 'parent_id');
    }

    public function childs()
    {
        return $this->hasMany(Term::class, 'parent_id', 'id');
    }
}

Recursion

function get_parent_term_name(Term $term, bool $root = true)
{
    $name = "";

    if (isset($term->parent)) {
        $name .= "— " . get_parent_term_name($term->parent, false);
    }

    return $root
        ? $name . " " . $term->name
        : $name;
}

Is there a way to achieve this?

Do you have a `parent()` and `children()` Relationship on your `Term` model? How are you loading the records recursively? To get that `-` and `- -`, your models need to be aware of how many Parent records they have. We're missing a lot of crucial information here. — Tim Lewis, Jul 11 '22 at 20:25
@TimLewis I added the `Term` model and the recursion function that build the name with dash — sfarzoso, Jul 12 '22 at 08:08
check the already answered question https://stackoverflow.com/a/72900714/4369919 — N69S, Jul 14 '22 at 09:22

score 1 · Answer 1 · answered Jul 15 '22 at 00:03

1

Your childs relation needs to be recursive to achieve this, for this, you can first fetch all the parents i.e. the records with parent_id = null since they will be the only records exiting as a parent but not child.

Term.php Model

public function parent()
    {
        return $this->hasOne(Term::class);
    }

public function childs()
    {
        //Recursively call childs() relation treating each 
        //child as parent until no more childs remain
        return $this->hasMany(Term::class, 'parent_id')->with('childs');
    }

TermsController.php

return Term::where('parent_id',null)->with('childs')->orderBy('id', 'ASC');

This will give you a nested collection as:

0 => [
    id: 1,
    name: 'Pop,,
    slug: 'pop',
    taxonomy:'category',
    parent_id:null,
    childs: []
]

1 => [
    id: 2,
    name: 'Rock,,
    slug: 'rock',
    taxonomy:'category',
    parent_id:null,
    childs: [
        id: 3,
        name: 'Jazz,,
        slug: 'jazz',
        taxonomy:'category',
        parent_id:2,
        childs: [
            id: 4,
            name: 'Edm,,
            slug: 'edm',
            taxonomy:'category',
            parent_id:3,
            childs: []
        ]
    ]
]

Note: Your code must have a termination condition because according to your requirements and this solution, the tree might end up in an infinite loop (provided your database contains billions of records)

answered Jul 15 '22 at 00:03

Salman Malik

923
6
24

You can also add ordering in `childs()` relation, so you won't have to do this in controller. – Styx Jul 15 '22 at 00:16
I think OP is asking about ordering of main nodes, i.e., the ones with no parent, and not the childs, even if we don't use ```orderBy()``` here it won't have any effect but I just left it there as is. – Salman Malik Jul 15 '22 at 00:20
@SalmanMalik good solution but the main goal is to returns all the rows ordered as `parent -> child` 'cause I need to display in datatables. I'm currently using this plugin: https://github.com/yajra/laravel-datatables – sfarzoso Jul 15 '22 at 07:45
I can't understand ```parent -> child``` do you have any visual representation of what you want to achieve here? Because from your original question you wanted a tree. – Salman Malik Jul 15 '22 at 07:53
@SalmanMalik I need a tree, but actually the childs shouldn't be nested within the parent. I just need them ordered as a list without nesting anything in parent – sfarzoso Jul 18 '22 at 06:57
@SalmanMalik gui example: https://cloud.githubusercontent.com/assets/7567800/19011341/c5124098-87bc-11e6-8760-7c9afb01a0d7.png another guy has asked this: https://github.com/yajra/laravel-datatables/issues/775 don't know if him has achieved that – sfarzoso Jul 18 '22 at 08:05
1

Well, fetching without nesting as a relation will be quite difficult since we will be uncertain about what relates what. So fetching it this way is our best bet right now. This nested data can however, be modified to generate the datable you require. Have you though about flattening the collection? try [this solution](https://stackoverflow.com/a/59905720/12138592) – Salman Malik Jul 18 '22 at 08:56
One flattened, the single array can easily be handled inside a single loop. – Salman Malik Jul 18 '22 at 08:58
@SalmanMalik I looked for the solution of the other post and it's interesting. But could you help me to convert the result in a laravel eloquent query builder? otherwise it won't work for the yajra datatable. Also, next time use the the tag in the comment, or I wouldn't get the notification on stack overflow. Hurry up, bunty is expiring ... – sfarzoso Jul 20 '22 at 15:11
@sfarzoso Hey, that answer is already well explained. I can't explain it any better. You have to define the helper functions and then simply call the methods in your controller class until the collection is flat. I can explain it to you but I don't really have enough time right now, and that by the way is a second problem so you might consider asking a solution for this in another post. – Salman Malik Jul 20 '22 at 15:29

score -1 · Answer 2 · answered Jul 12 '22 at 18:23

-1

orderByRaw('parent_id ASC, name Asc')

answered Jul 12 '22 at 18:23

Rabie

52
2

How to order by child / parent column with Eloquent?

2 Answers2