CODEWITHSUNDEEP
laraveleloquentlaravel-8yajra-datatable
sfarzoso
sfarzoso

Reputation: 1600

How to order by child / parent column with Eloquent?

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?

Upvotes: 4

Views: 1692

Answers (2)

Salman Malik
Salman Malik

Reputation: 1118

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)

Upvotes: 2

Rabie
Rabie

Reputation: 52

orderByRaw('parent_id ASC, name Asc')

Upvotes: -1

Related Questions