php – Laravel ORM select and whereBetween-ThrowExceptions

Exception or error:

I have next code:

$properties = $properties
->selectRaw('*,'. $this->userCurrency->c_rate .' / c_rate  * p_fixed_price AS 
converted_p_fixed_price');

after that I want to sort by this price.

 $properties = $properties->whereBetween('converted_p_fixed_price',
     [$request->low_price ,$request->hight_price]
 );

But in result i got Column not found: 1054
Please help, how to whereBetween that field in right way?

How to solve:

As referred Here :An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column, Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined, so you must use having instead of whereBetween.

The second part of your code can be something like this:

 $properties = $properties
        ->having('converted_p_fixed_price', '>=', $request->low_price)
        ->having('converted_p_fixed_price', '<=' ,$request->hight_price);

As you can not use pagination with having clauses in Laravel, if you want to paginate results, you can use something like this:

$properties = $properties
    ->whereRaw($this->userCurrency->c_rate . ' / c_rate  * p_fixed_price >= ' . $request->low_price)
    ->whereRaw($this->userCurrency->c_rate . ' / c_rate  * p_fixed_price <= ' . $request->hight_price)
    ->paginate($page_length);

Leave a Reply

Your email address will not be published. Required fields are marked *