从自引用表中获取Laravel ORM中其他表的列获取N级层次结构JSON

Question

I have two tables abc and def. I have models of these two tables named respectively Abc and Def. In Abc, I declare these two functions:

public function child()
{
   return $this->hasMany('Abc', 'parent');
}

// recursive, loads all descendants
public function children()
{
   return $this->child()->with('children')->orderBy('sort_order');  
}

// parent
public function parent()
{
   return $this->belongsTo('Abc','parent');
}

// all ascendants
public function parentRecursive()
{
   return $this->parent()->with('parentRecursive');
}

In controllers I called these functions by

$abc = Abc::with('children')->whereNull('parent')->get();

I have implemented level hierarchy JSON from table through parent child relation. I have common column name in both tables. Through these method I get the title column of abc table. But I want to fetch title column of def table. How we can do this?

Answer 1

Using relation:

// Abc model
public function defs()
{
  return $this->hasMany('Def', 'abc_id');
}

public function childrenWithDef()
{
  return $this->child()->with('childrenWithDef.defs')->orderBy('sort_order');
}

// then:
$abc = Abc::with('childrenWithDef', 'defs')->whereNull('parent')->get();

// every Abc has related Def collection
foreach ($abc->first()->defs as $def) $def->title

or manually joining on the relation and selecting whatever you need:

public function childrenWithTitle()
{
   return $this->child()->with('children')->orderBy('sort_order')
     ->leftJoin('defs', 'defs.abc_id', '=', 'abcs.id')->select('abc.*', 'def.title');
}

Remember that you need keys/foreign keys to be selected in order to let Eloquent match children to their parents!

$abc = Abc::with('childrenWithTitle')->whereNull('parent')->leftJoin(...)->first();
$abc->childrenWithTitle->first()->title; // 'title' or null if no row found for this child