Laravel Question posted in
The official Laravel Documentation can be found here.

LARAVEL orderBy DESC not working while using groupBy

I would like to use orderBy in laravel but its not working while I have used CAST there

    $this->adLists = ListingVisitor::with('listing')->selectRaw('CAST(sum(amount) as UNSIGNED) as total,listing_id,amount')->whereHas('listing.user',function($q){
        $q->where('id',Auth::user()->id);
    })->groupBy('listing_id')->orderBy('total','DESC')->paginate(10);

The result would be

136, 66, 1 ,5

Its actually should be 136, 66, 5, 1

I have using CAST in selectRaw but it has no difference

Tags:

2

Answers


  1. 0 
    $this->adLists = ListingVisitor::with('listing')
    ->selectRaw('sum(amount) as total, listing_id')
    ->whereHas('listing.user', function($q) {
        $q->where('id', Auth::user()->id);
    })
    ->groupBy('listing_id')
    ->orderBy(DB::raw('sum(amount)'), 'DESC')
    ->paginate(10)

    I think there is no requirement to cast it. When you do sum it already return you with the numeric format. Instead try to Order with the SUM function. It will give you the expected output.

    Login or Signup to reply.
  2. 0

    You can use DB::raw, This will solve any issue with ordering by the aliased column when using CAST in laravel

    use IlluminateSupportFacadesDB;

$this->adLists = ListingVisitor::with('listing')
    ->select(DB::raw('CAST(SUM(amount) AS UNSIGNED) AS total'), 'listing_id', 'amount')
    ->whereHas('listing.user', function ($q) {
        $q->where('id', Auth::user()->id);
    })
    ->groupBy('listing_id')
    ->orderBy(DB::raw('total'), 'DESC')
    ->paginate(10);
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search