Laravel如何在单个表上添加所有总数

Question

我正在尝试构建一个查询，但有问题。我想编写一个代码，每行都在一个表上都有全部计数。我将首先描述

我将查询总数：

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

//the all total counts of this ratings table is 12

第二是在与作者的评分中查询每行计数：

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

输出：

[
  {
  "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": "[email protected]"
                }
          }
  },
  {
  "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": "[email protected]"
          }
      }
  }
]

您可以在此代码中看到每行拥有他们的 rating_count_count_count in <代码> ID：1 具有 rating_count 5，在ID中：2在总计12时具有评分计数7。

现在我的问题的目的是我想要在book :: withCount中添加 addSelect（） 我想添加第一个查询我写的。因此，每一行的总书籍总数为12，

但我尝试了此代码，但它给出了一个错误：

   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();

错误：

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`)

这是我的表格：（我没有在作者和评分中添加ROW create_at和updated_ad），

my table authors

id    |   user_id   
1     |   1        
2     |   5          


my table books 

id    |  created_at | updated_at | author_id   |  title   |   name
1     |             |            |   1         |  vel     |   Qui odit eum ea recusandae rem officiis
2     |             |            |   2         |  atque   |   Beatae tenetur modi rerum dolore facilis eos incidunt.
 
my table ratings

id    |   rating   |  book_id   
1     |   5        |    1  
2     |   4        |    1  
3     |   4        |    1  
4     |   3        |    1  
5     |   2        |    1  
6     |   1        |    1  
7     |   1        |    1  
8     |   5        |    2  
9     |   4        |    2  
10    |   3        |    2  
11    |   3        |    2  
12    |   1        |    2 

这是我的模型

模型作者

class Author extends Model
{
    use HasFactory;

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

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

模型模型书籍。

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);
    }
}

Answer 1

我不太了解 total_books的计数的查询

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

上述查询本质上是评级表中的记录计数表。由于评分中的记录表将具有 book_id （假设已定义完整性约束）的有效值，这意味着对于评级中的任何行/记录表 book_id 中包含的价值将是 book 表中现有记录的 id 。

因此，其中（'book_id'，book :: select（'id'））是不必要的。您可以做到，

$count = Rating::count();

//Which will output the same result as
//$count = Rating::whereIN('book_id',Books::select('id'))->count();

然后可以使用 addSelect

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

或使用 selectraw

$books = Books::query()
    ->withCount('rating')
    ->with(['author:id,user_id','author.user:id,name,email'])
    ->selectRaw('? as total_ratings', [Rating::count()])
    ->get();

total_books 可能被称为 total_ratings < /strong>

如果您仍然想拥有，其中约束（这不是必要的），则

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

可以生成SQL

select `books`.*, 
  (select count(*) from `ratings` where `books`.`id` = `ratings`.`book_id`) as `rating_count`, 
  (select count(id) from `ratings` where `book_id` in (select `id` from `books`)) as `total_books` 
from `books`

或使用 selectraw

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

Answer 2

正如我评论的那样，您可以先计数总评分，然后将其重新分配到$ book实例。如果不需要，请不要让SQL查询为每个获取的行计算。

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

$count = Rating::query()
    ->whereIn('book_id', $books->pluck('id')->toArray())
    ->count();

foreach ($books as $book) {
    $book->rating_count = $count;
}