/**
* Query get book that is recently published.
* @param [Illuminate\Database\Eloquent\Builder] $query
* @return [Illuminate\Support\Collection]
*/
public function scopeBookWithLanguageAndCategory($query, $languageId, $categoryId)
{
$query->where('books.is_published', Book::PUBLISH);
if ($languageId != "all") {
$query->join('languages', function ($join) use($languageId) {
$join->on('languages.id', '=', 'books.language_id')->where('languages.id', '=', $languageId);
});
}
if ($categoryId != "all") {
$query->join('book_category', function ($join) use($categoryId) {
$join->on('book_category.book_id', '=', 'books.id')->where('book_category.category_id', '=', $categoryId);
});
}
return $query;
}
/**
* Query get bundle that is recently published.
* @param [Illuminate\Database\Eloquent\Builder] $query
* @return [Illuminate\Support\Collection]
*/
public function scopeBundleRecentlyIsPublished($query)
{
return $query->join('book_bundle', function ($join) {
$join->on('book_bundle.bundle_id', '=', 'bundles.id')->where('book_bundle.accepted', '=', self::ACCEPT);
})->join('books', function ($join) {
$join->on('books.id', '=', 'book_bundle.book_id')->where('books.is_published', '=', Book::PUBLISH);
})->leftJoin('popularity', function ($join) {
$join->on('popularity.item_id', '=', 'bundles.id')->where('popularity.type', '=', Popularity::TYPE_BUNDLE);
})->groupBy(['bundles.id', 'popularity.type'])->select(['bundles.*', DB::raw('COUNT(*) as views'), DB::raw('concat("[\\"", GROUP_CONCAT(distinct books.diravatar SEPARATOR "\\",\\""), "\\"]") AS avatar')])->orderBy('published_at', 'desc');
}
