Laravel how to addSelect all total counts on a single table each row-CodePudding

i am trying to build a single query but something is wrong. i want to write a code that each row have a all total count on a one table. i will describe

first i will query the total counts :

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

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

second is querying the books count each rows in ratings with authors :

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

the output of this :

[
  {
  "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]"
          }
      }
  }
]

you can see in this code each row has own their rating_count in id:1 has rating_count 5 and in id:2 has rating count 7 when summing them total of 12.

now the point of my problem is i want to add addSelect() in the Book::withCount i want to add the first query i wrote. so each row has a total books of 12

i tried this code but it gives a error:

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

the error:

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

here is my tables: ( i did not add row created_at and updated_ad in authors and ratings )

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

here is my models

model Authors

class Author extends Model
{
    use HasFactory;

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

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

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

CodePudding user response：

I don't quite understand the query for count of total_books

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

The above query is essentially the count of records in the ratings table. Since the records in ratings table will have a valid value for book_id (assuming integrity constraints are defined) which means that for any row/record in the ratings table the value contained in book_id will be an id of an existing record in books table.

So the whereIn('book_id', Book::select('id')) is unnecessary. You can do just

$count = Rating::count();

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

Then you can have your composite query with addSelect as

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

Or using selectRaw

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

The total_books should probably be named as total_ratings

If you still want to have your whereIn constraint (which isn't necessary) you can

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

The above will generate an 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`

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

CodePudding user response：

As I commented, you can count total ratings first, then reassign it to $book instance. Don't let SQL query calculates for every fetched rows if unnecessary.

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