Laravel使用addSelect为查询结果每行添加单表全局总计数

需求说明 #

查询书籍列表时，为返回结果的每一行附加ratings表中关联有效书籍的全局评分总计数，基于原有书籍查询逻辑扩展时触发SQL报错。 #

问题复现 #

可独立运行的基础逻辑 #

• 单独查询ratings表关联有效书籍的评分总条数，可得到正确结果12，代码如下：

$count = Rating::whereIN('book_id',Books::select('id'))->count();
// ratings表全局有效评分总计数为12

• 原有书籍列表查询可正常执行：查询所有书籍，通过withCount统计单本书关联的评分数量，同时预加载作者及作者绑定的用户信息，代码如下：

return  $books = Books::withCount('rating')
        ->with(['author:id,user_id','author.user:id,name,email'])
        ->get();

正常返回结果示例如下，两本书的rating_count相加正好等于全局总计数12：

[
  {
  "id": 1,
  "created_at": "2022-06-15T09:59:10.000000Z",
  "updated_at": "2022-06-15T09:59:10.000000Z",
  "author_id": 2,
  "title": "vel",
  "name": "Qui odit eum ea recusandae rem officiis.",
  "rating_count": 5,
  "author": {
          "id": 2,
          "user_id": 1,
          "user": {
                  "id": 1,
                  "name": "Joshua Weber",
                  "email": "bhessel@example.com"
                }
          }
  },
  {
  "id": 2,
  "created_at": "2022-06-15T09:59:10.000000Z",
  "updated_at": "2022-06-15T09:59:10.000000Z",
  "author_id": 1,
  "title": "atque",
  "name": "Beatae tenetur modi rerum dolore facilis eos incidunt.",
  "rating_count": 7,
  "author": {
          "id": 1,
          "user_id": 5,
          "user": {
                "id": 5,
                "name": "Miss Destinee Nitzsche III",
                "email": "jamir.powlowski@example.net"
          }
      }
  }
]

报错触发代码 #

尝试在原有查询中调用addSelect附加全局总计数字段，直接传入提前计算的count结果，代码如下：

return  $books = Books::withCount('rating')
        ->with(['author:id,user_id','author.user:id,name,email'])
        ->addSelect(['total_books'=>Rating::whereIN('book_id',Books::select('id'))->count()])
        ->get();

执行后抛出SQL错误，生成的SQL语句将统计得到的数值当做字段名查询，触发字段不存在错误：

SQLSTATE[42S22]: Column not found: 1054 Unknown column '105' in 'field list' (SQL: select `books`.*, (select count(*) from `ratings` where `books`.`id` = `ratings`.`book_id`) as `rating_count`, `105` from `books`)

相关基础信息 #

表结构 #

• authors表（省略created_at、updated_at字段）：

• books表：

• ratings表（省略created_at、updated_at字段）：

模型定义 #

• Author模型：

class Author extends Model
{
    use HasFactory;

    public function books(){
        return $this->hasMany(Books::class);
    }

    public function User(){
        return $this->belongsTo(User::class);
    }
}

• Books模型：

class Books extends Model
{
    use HasFactory;

    protected $casts = [
        'created_at' => 'datetime',
    ];

    public function rating(){
        return $this->hasMany(Rating::class,'book_id');
    }

    public function author(){
        return $this->belongsTo(Author::class);
    }
}

报错原因 #

addSelect方法接收数组格式参数时，默认将数组值解析为数据库字段名或查询构造器实例，不会自动处理传入的标量值。直接传入提前计算得到的整数时，框架会直接将该值拼接到SELECT子句中，MySQL会将无引号包裹的数字识别为字段名，最终抛出字段不存在的错误。 #

正确实现方案 #

方案1：通过DB::raw传入计算好的标量值 #

提前计算全局总计数，通过原生SQL表达式传入字段，避免框架将数值识别为字段名：

$totalRatings = Rating::whereIn('book_id', Books::select('id'))->count();

return $books = Books::withCount('rating')
        ->with(['author:id,user_id','author.user:id,name,email'])
        ->addSelect(DB::raw("{$totalRatings} as total_ratings"))
        ->get();

方案2：传入子查询构造器实现单SQL查询 #

不需要提前执行count查询，直接返回查询构造器实例，框架会自动将其编译为SELECT子句中的子查询，单条SQL即可完成所有数据查询：

return $books = Books::withCount('rating')
        ->with(['author:id,user_id','author.user:id,name,email'])
        ->addSelect([
            'total_ratings' => Rating::whereIn('book_id', Books::select('id'))
                                    ->selectRaw('count(*)')
        ])
        ->get();

两种方案返回的每一条书籍记录都会携带total_ratings字段，值为全局有效评分总计数，完全匹配需求。

内容的提问来源于stack exchange，提问作者Ray Paras