Laravel Eloquent put all rows returned that have a column value of 0 at the end no matter the sorting

Question

I have a builder query that pulls info depending on info entered. The main tables are hotels and rates. The user can sort the results with price high to low and price low to high. I need to put all results that return 0 ( some rate date ranges have place holders with 0 as the price ) or hotels that dont have rates at the end of the results no matter what. How can I handle making those both show up at the end regardless of the sorting. I have the below as where I am now but this doesnt handle things if it doesnt exist only if its 0 but it also doesnt handle putting the results at the end.

One more thing, Im using pagination or else I would just do a foreach and reorder them.

$builder->select('hotels.id','hotels.main_image','hotels.sleeps','hotels.bathrooms','hotels.destination_id','hotels.hotel_name','hotels.slug','hotels.summary','hotels.sleeps','hotels.bedrooms',DB::raw('CASE WHEN max(rates.nightly_rate) = 0 THEN NULL ELSE max(rates.nightly_rate) END AS price_high'),DB::raw('CASE WHEN min(rates.nightly_rate) = 0 THEN NULL ELSE min(rates.nightly_rate) END AS price_low')
->leftJoin('rates', 'hotels.id', '=', 'rates.hotels_id');

Answer 1

It is possible to enforce records where nightly_rate is 0 or null to be ordered last in order by clause.

A clause like rates.nightly_rates = 0 or rates.nightly_rate IS NULL ASC will ensure that matched records where this condition is FALSE are ordered first before records where it is TRUE.

$builder
->
//...
->orderByRaw('rates.nightly_rate = 0 OR rates.nightly_rate IS NULL ASC');