Laravel Eloquent Order By Coalesce

I am having issues using a coalesce inside an orderby to order on 2 relationship columns. Here is the code in question:

$productList = Product::select(
'ItemName'
,'ItemCode'
,'QryGroup64'
,'U_SDB_Image_File'
)->active();

if ($this->search){
$productList = $productList->where('ItemName', 'LIKE','%'.str_replace(" ", "%", $this->search).'%');
}

if ($this->category){
$productList = $productList->where('U_SDB_SALES_GROUP',$this->category);
}

// Handle the order by. It is done like this because of the pagination
if ($this->orderBy == 'ItemNameASC'){
$productList = $productList->with('customerSpecialPrice','bandAPrice','itemStock','itemsPurchased8Weeks')
->orderBy('ItemName');
} elseif ($this->orderBy == 'ItemNameDESC'){
$productList = $productList->with('customerSpecialPrice','bandAPrice','itemStock','itemsPurchased8Weeks')
->orderBy('ItemName', 'desc');
} elseif ($this->orderBy == 'PriceASC'){
$productList = $productList->with('itemStock','itemsPurchased8Weeks')
->withAggregate('bandAPrice', 'Price')
->withAggregate('customerSpecialPrice', 'Price')->orderByRaw("COALESCE(customer_special_price_price, band_a_price_price)");
}

$productList = $productList->paginate($this->perPage);


This is for product browsing. Every item has a Band A price (default sell out price). There is another table that has our quoted products which means that there isn't always a special price for every item.

Now, if I do: ->orderBy("customer_special_price_price"); , This works to an extent. All of the special prices are in order but at the end of the list (due to null being pulled first). This is why I am wanting to use a COALESCE.

Errors I have gotten: When trying: ->orderByRaw("COALESCE(customer_special_price_price, band_a_price_price)");, it gives me the error Invalid column name 'customer_special_price_price'.

Below is the query it is trying to run when it shows this error:

select
top 20 [ ItemName ],
[ ItemCode ],
[ QryGroup64 ],
[ U_SDB_Image_File ],
(
select
top 1 Price
from
[ ITM1 ]
where
[ OITM ].[ ItemCode ] = [ ITM1 ].[ ItemCode ]
and [ PriceList ] = 25
) as [ band_a_price_price ],
(
select
top 1 Price
from
[ OSPP ]
where
[ OITM ].[ ItemCode ] = [ OSPP ].[ ItemCode ]
and [ CardCode ] = CF000097 - F
) as [ customer_special_price_price ]
from
[ OITM ]
where
SellItem = 'Y'
AND (
QryGroup64 = 'N'
OR OnHand > 0
)
and [ ItemName ] LIKE % carling %
order by
COALESCE(customer_special_price_price, band_a_price_price)


Why is orderBy picking up the column created via the withAggregate but the orderByRaw isnt?

Continue reading...