如何在Laravel Eloquent中对同一个表进行子查询 - How to do Subquery for same Table in Laravel Eloquent

How to do Subquery for same Table in Laravel Eloquent

本文关键字：查询同一个 Laravel Eloquent | 更新日期: 2023-09-29

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

我的表格结构是：

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'

您可以在同一个表上添加Eloquent关系，并将其过滤到右侧的catType:

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

这将使项目的子项可用作$item->children。你可以得到这样的计数：

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