/** * 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'); }