[FIXED] Laravel order by price with condition special price

Issue

I have table with next columns (price, special_price, is_special).

+-------+------+-----------------+--------------+---------+
| id    | price |  special_price  | is_special   | qty      |
+-------+-------------------------+--------------+----------+
| 1     | 100   |    null         | 0            |  5       |
| 2     | 120   |    99           | 1            |  0       |
| 3     | 300   |    null         | 0            |  1       |
| 4     | 400   |    350          | 1            |  10      |
| 5     | 75    |    69           | 1            |  0       |
| 6     | 145   |    135          | 0            |  1       |
+-------+-------+-----------------+--------------+---------+

I want to get products ordered by 'price' with condition, if 'is_special' column is true then select 'special_price' column.

I want to get next result.

+-------+-----------+-----------------+--------------+--------------+
| id    | price     |  special_price  | is_special   | qty          |
+-------+-----------------------------+--------------+--------------+
| 5     | 75        |    69           | 1            |  0           |
| 2     | 120       |    99           | 1            |  0           |
| 1     | 100       |    null         | 0            |  5           |
| 6     | 145       |    135          | 0            |  1           |
| 3     | 300       |    null         | 0            |  1           |
| 4     | 400       |    350          | 1            |  10          |
+-------+-----------+-----------------+--------------+--------------+

On raw SQL it's looks like

SELECT *
FROM products
ORDER BY IF(is_special=0, price, special_price ) ASC;

I using Laravel and want to ordered and get query builder in result.

For example I did it with virtual attributes

/**
 * Get current price
 *
 * @return mixed
 */
 public function getCurrentPriceAttribute()
 {
     return $this->is_special ? $this->special_price : $this->price;
 }

And sorted collection $products->sortBy('current_price') but in this time I want to get query builder in result. Query builder not working with virtual attributes.

I'm trying to multiple sorting by two columns 'price' and 'qty'

$query = Product::query();

$query->orderByRaw("if(is_special=0, price, special_price) " . request('price', 'ASC'));
$query->orderBy('qty', request('qty', 'DESC'));

$query->get();

I have 2 filter 'Quantity' and 'Price'.

In this multiple ordering I want to get products ordering by price, then all products ordering by 'qty'. Products with qty == 0, need to be next after all products with qty > 0.

Help me, please.

Solution

The First Problem

Query-Builder has no accessors, you need to select it out:

DB::table('products')
   ->select('*')
   ->addSelect(DB::raw('IF(is_special=0, price, special_price ) AS current_price'))
   ->orderBy('current_price')
   ->get();

PS:Recommend to sort in database, think about if you have paginate on products, it will sort only on the one page's datas when it return that page.

---

The second problem:

1. qty > 0 AS 1, and qty = 0 AS 0, then order them DESC:

2. Order by price with request

3. Order by qty with request

So the products will put the qty > 0 before qty = 0, and the records that qty > 0 will ordering by price, then all products ordering by qty; and the records that qty = 0 will ordering by price, then all products ordering by qty too:

$query = Product::query();
$query->orderBy(DB::raw(IF('qty > 0, 1, 0')), 'DESC');
$query->orderBy(DB::raw("IF(is_special=0, price, special_price)"), request('price', 'ASC'));
$query->orderBy('qty', request('qty', 'DESC'));
$query->get();

PS: orderByRaw("if(is_special=0, price, special_price) " . request('price', 'ASC') will be attack easily by SQL-Injection. Change to orderBy(DB::raw("IF(is_special=0, price, special_price)"), request('price', 'ASC'))

Answered By - TsaiKoga