i has raw query in laravel like this

public function getPopularBook(){
        $book = DB::select("
                    with totalReview as(
                        SELECT r.book_id , count(r.id)
                        FROM review r
                        GROUP BY r.book_id
                    )
                    SELECT *
                    from totalReview x
                    left JOIN (
                         SELECT b.*,
                                case when ((now() >= d.discount_start_date and now() <= d.discount_end_date) or (now() >= d.discount_start_date and d.discount_end_date is null)) then (b.book_price-d.discount_price)
                                ELSE b.book_price
                                end as final_price
                        FROM discount d
                        right JOIN book b
                        on d.book_id = b.id
                         ) as y
                    on x.book_id = y.id
                    ORDER BY x.count DESC, y.final_price ASC
                    LIMIT 8"
        );
        return $book;
    }

so when i want to return a paginate, it doesn't work so can i convert this to query build to use paginate

CodePudding user response：

This is a very un-optimized raw query in itself. You are performing too many Join in Subquery just to sort by price

i'm assuming the database table:

books[ id, name, price ]
reviews[ id, book_id ]
discounts[ id, book_id, start_date, end_date, discount_price]

Look how easy it is if you just use Eloquent:

Book::withCount('reviews')->orderBy('reviews_count')->get();

this will give you all the Books order by number of reviews

now with the final price, this can be a bit tricky, let's take a look at a query when we don't consider discount time

Book::withCount('reviews')
    ->withSum('discounts', 'discount_price')  //i'm assuming a book can have many discount at the same time, so i just sum them all
    ->addSelect(
        DB::raw('final_price AS (books.price - discounts_sum_discount_price)')
    )
    ->orderBy('reviews_count', 'asc')    // =you can specify ascending or descending
    ->orderBy('final_price', 'desc')    //in laravel chaining multiple orderBy to order multiple column
    ->get();

I dont even need to use Subquery!! But how do we actually only add the "active" discount?, just need to modify the withSum a bit:

Book::withCount('reviews')
    ->withSum(
         [
             'discounts' => function($query) {
                 $query->where('start_date', '<=', Carbon::now())
                       ->where('end_date', '>=', Carbon::now())
              }
         ],
         'discount_price'
    )
    ->addSelect(
        DB::raw('final_price AS (books.price - discounts_sum_discount_price)')
    )
    ->orderBy('reviews_count', 'asc')    // =you can specify ascending or descending
    ->orderBy('final_price', 'desc')    //in laravel chaining multiple orderBy to order multiple column
    ->get();

and it is done

What about pagination? just replace the get() method with paginate():

Book::withCount('reviews')
    ->withSum(['discounts' => function($query) {
                 $query->where('start_date', '<=', Carbon::now())->where('end_date', '>=', Carbon::now())
              }],'discount_price')
    ->addSelect(DB::raw('final_price AS (books.price - discounts_sum_discount_price)'))    //just format to be a bit cleaner, nothing had changed
    ->orderBy('reviews_count', 'asc')
    ->orderBy('final_price', 'desc')
    ->paginate(10);    //10 books per page

WARNING: this is written with ELoquent ORM, not QueryBuilder, so you must define your relationship first