Sort an Eloquent model-CodePudding

Table for MyModel is like below

id      name        parent_id
--------------------------
1      parent1       null
2      p1-c1         1
3      parent2       null
4      p1-c1-g1      2
5      p2-c1         3
6      p1-c1-g1-f1   4
7      parent3       null
8      p1-c2         1
9      p3-c1         7
10     p1-c2-c1      8

How order with eloquent or raw DB query without getting all rows like below

Collection {#499 ▼
  #items: array:22 [▼
    0 => MyModel {#524 ▶} // parent1
    1 => MyModel {#525 ▶} // p1-c1
    2 => MyModel {#526 ▶} // p1-c1-g1
    3 => MyModel {#527 ▶} // p1-c1-g1-f1
    4 => MyModel {#528 ▶} // p1-c2
    5 => MyModel {#529 ▶} // p1-c2-c1
    6 => MyModel {#530 ▶} // parent2
    7 => MyModel {#531 ▶} // p2-c1
    8 => MyModel {#532 ▶} // parent3
    9 => MyModel {#533 ▶} // p3-c1
  ]
}

for a better view and understanding

parent1
p1-c1
p1-c1-g1
p1-c1-g1-f1
p1-c2
p1-c2-c1
parent2
p2-c1
parent3
p3-c1

CodePudding user response：

use Recursive relationship to same table

For example if model name is MyModel then the relationship will be

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

public function recursiveChild(){
    return $this->child()->with('recursiveChild');
}

So in your controller

MyModel::with(['recursiveChild'])->get();

CodePudding user response：

What is your actual code, what are you trying to accomplish with this? Collections are your friend in Laravel. You can do a groupBy or using a With if you have a table that's related... your question is really too ambiguous. You can get fairly complex with many chained Where orWhere with limits and groupBy What I do see is that you need to have is not only the parent in the db but you also need a sort order on the children. Another thought is do you need a Grandparent, Parent, child? In that case you're going to have to have columns for all of those to know who they belong to when building your collection or doing just a straight eloquent pull. My guess is this is for a menu or something you have here. Again ambiguity, so totally guessing.

EDIT I've done this kind of thing in several apps... You're table is going to look something like this:

id, name, menu_type, url, page_id, parent_id, sidebar, order, depth, role, created_at, updated_at, deleted_at

So in your model your going to want to do a relationship:

  public function parent()
  {
      return $this->belongsTo('App\Models\Menus', 'parent_id');
  }

  public function children()
  {
      return $this->hasMany('App\Models\Menus', 'parent_id')->orderBy('order', 'asc');
  }

Then what I've done is use this as a trait so it's available wherever I need to use it.

The trait is a little more complex than you are asking but it points you in the direction.

 public function PrimaryMenu($pageid , $namespace, $user) {
        // Get all primary menu items.
        $appspace = "\App\Models\\".$namespace."\\Menus";
        $roles = $user->roles->pluck('name');
        $pmenus = $appspace::with('children')->where([['menu_type', '=', 'primary'],['parent_id','=', NULL]])->
        where(function ($q) use($pageid) {
            $q->where('page_id', '=', $pageid)->orWhere('page_id', NULL);
        })->
        where(function ($q2) use($roles) {
            $q2->whereIn('role', $roles)->orWhere('role', '=', NULL);
        })->
        get();

        return $pmenus;
    }

You can see I'm checking a type of menu (this can be anything as long as you have something to group them by category) in this case primary, then I check for the page and see if we should include a row or not on the page then I check for any roles the user might have that we should include items.

Then in your controller it's just a matter of using the trait at the top:

 use App\Traits\CommonTrait;

Now all of those traits are available to use in your controller. So you just call the function you need:

$pmenus = $this->PrimaryMenu($pageid, $namespace, $user);

You can see I'm sending along 3 variables to this as I have this set up for having many menus. Really the code translates to anything though that you want children associated with a parent.

The important bits are the model actions on relationships.