如何使用Eloquent按子/父列排序?

Question

我有一个名为terms的表，其中包含以下字段:

id | name | slug | taxonomy | parent_id

这是当前数据源:

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

本质上，我想要返回像这样的结果:

Pop
Rock
— Jazz
— — Edm

因为Jazz和Edm是Rock的子结点，所以我这样写:

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

问题是，当我按特定列订购数据表时，我没有得到所需的顺序，Eloquent将返回按所选列排序的记录，例如:

column: name
order: desc

结果:

Rock
Pop
— Jazz
— — Edm

预期结果:

Rock
— Jazz
— — Edm
Pop

<<p>项模型/strong>

<?php
namespace AppCmsModels;
use IlluminateDatabaseEloquentModel;
use IlluminateDatabaseEloquentFactoriesHasFactory;
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');
}
}

递归

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;
}

有办法做到这一点吗?

Answer 1

您的childs关系需要递归实现这一点，为此，您可以首先获取所有父级，即parent_id = null的记录，因为它们将是唯一作为父级而不是子级的记录。

Term.php模型

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');

这将给你一个嵌套集合:

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:你的代码必须有一个终止条件，因为根据你的需求和这个解决方案，树可能会在一个无限循环中结束(假设你的数据库包含数十亿条记录)

Answer 2

orderByRaw('parent_id ASC, name ASC ')