Clear Laravel's orderBy

Question

I have a generic function which gives me generic querysets, something like:

class Model extends Eloquent {
  public static function get_queryset(){
    $queryset = self::where('foo','=','bar');
    // Do tons of stuff with the query and then...
    return $queryset->orderBy('somefield');
  }
}

This function is used everywhere my project, but in a specific point I need to use this queryset but changing the ORDER BY, much like this:

public static function get_specific_field(){
  return self::get_queryset()->select('singlefield')->orderBy('singlefield');
}

If I run this code, the ORDER BY will just append onto the previous and generate an invalid query, since the "somefield" is not on the SELECTed fields. i.e.:

SELECT singlefield FROM table ORDER BY somefield ASC, singlefield ASC

How do I clear the orderBy so I can just reuse querysets?

Answer 1

I agree there should be a clearOrderBy() method added to the query builder. However because the registry of orderbys is a public property on Illuminate\Database\Query\Builder you actually can clear it yourself today. The trick is getting access to the base query object:

$query = YourModel::where('status', 1)->orderBy('created_at','desc');
// ... lots of other code, something needs to reset the order by ...
$query->getQuery()->orders = null;
$query->orderBy('other_column', 'desc');

At other times, for example when manipulating a relation query, you need to get to access the base query (Illuminate\Database\Query\Query). So for example:

$query = YourModel::find(1)->load('children', function ($query) {
    $query->getBaseQuery()->orders = null;
});

Thats it. I plan to submit a PR for a clearOrderBy() as well.

Answer 2

In Laravel 7, there's now a method to remove the orders from the query builder (#32186):

public static function get_specific_field(){
    return self::get_queryset()->select('singlefield')->reorder('singlefield');
}

Answer 3

Since this is a pretty old question and probably most of you have already upgraded to Laravel 7 and above, you can use the built-in reorder() method as @halloei mentions (#32186).

However, if you are stuck with Laravel bellow version 7, you can make a macro using the idea from the PR. Then you can use reorder() as you would with Laravel 7. Just register that in the AppServiceProvider@boot.

\Illuminate\Database\Query\Builder::macro('reorder', function ($column = null, $direction = 'asc') {
   $this->orders = null;
   $this->unionOrders = null;
   $this->bindings['order'] = [];
   $this->bindings['unionOrder'] = [];

   if ($column) {
      return $this->orderBy($column, $direction);
   }

   return $this;
});

Answer 4

Why not "genericise" your queryset?

class Model extends Eloquent {
  public static function get_queryset($orderBy = 'somefield'){
    $queryset = self::where('foo','=','bar');
    // Do tons of stuff with the query and then...
    return $queryset->orderBy($orderBy);
  }
}

Then you can use

public static function get_specific_field(){
  return self::get_queryset('singlefield')->select('singlefield');
}

Answer 5

Here is a simple solution , tiny package you can use it by simply calling ->clearOrdersBy() https://github.com/phpfalcon/laravel-clear-orders-by

Answer 6

Off the back of @halloei answer which can be construed as a little unclear, you can simply do the below which will clear all existing orders, at the same time as applying new orders.

// get all users and sort by ID
$builder = User::query()->orderBy('id');

// now scrap that order and sort by email
$builder->reorder('email');