/**
* Query get views book in week.
* @param [Illuminate\Database\Eloquent\Builder] $query
* @return [Illuminate\Support\Collection]
*/
public function scopePopularBookLifetime($query)
{
return $query->leftJoin('popularity', function ($join) {
$join->on('popularity.item_id', '=', 'books.id')->where('popularity.type', '=', Popularity::TYPE_BOOK);
})->join('book_author', 'book_author.book_id', '=', 'books.id')->join('users', function ($join) {
$join->on('users.id', '=', 'book_author.author_id')->where('book_author.is_main', '=', 1);
})->where('books.is_published', Book::PUBLISH)->groupBy('books.id')->select(['books.*', 'users.firstname', 'users.lastname', DB::raw('COUNT(*) as views')])->orderBy('views', 'desc');
}
