php - 如何在 Laravel Eloquent 中对同一张表进行子查询

Question

我构建了一个类别表来存储类别和子类别。

我的表结构是:

catID (int)
catType (varchar 4)
parentID (int)
catName (varchar 50)
sOrder (int)

如何使用 Eloquent 进行后续子查询。

SELECT c.*, 
(
    SELECT COUNT(*) FROM categories AS sc WHERE sc.parentID = c.catID AND sc.catType = "Sub" 
) AS totalSubCategories
FROM categories AS c 
WHERE c.catType = 'Root' 

我试过以下但它不会工作:

$rows = CategoryModel::from('categories as c')
        ->where('c.catType', '=', 'Root')
        ->paginate(20)
        ->select(DB::raw('c.*, (SELECT COUNT(*) FROM categories as sc WHERE sc.parentID = c.catID AND sc.catType = "Sub") AS totalCategories'));

我遇到以下错误

ErrorException (E_UNKNOWN) 
call_user_func_array() expects parameter 1 to be a valid callback, class 'Illuminate\Support\Collection' does not have a method 'select'

Answer 1

可以在同一张表上添加Eloquent关系，过滤到合适的catType:

public function children() {
  return $this->hasMany('Model', 'parentID')->where('catType', 'Sub');
}

这使得该项目的子项可用作 $item->children。你可以像这样得到一个计数:

$count = $item->children()->count();