I have a table with the following columns (price, special offer, whether on sale).
+-------+------+-----------------+--------------+---------+ | 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 the products sorted by 'price', and if the 'is_special' column is true, select the 'special_price' column.
I want the following results.
+-------+-----------+-----------------+--------------+--------------+ | 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 | +-------+-----------+-----------------+--------------+--------------+
In raw SQL it looks like
SELECT * FROM products ORDER BY IF(is_special=0, price, special_price ) ASC;
I'm using Laravel and want to sort the query builder and get the results.
For example, I did this using virtual properties
/**
* 获取当前价格
*
* @return mixed
*/
public function getCurrentPriceAttribute()
{
return $this->is_special ? $this->special_price : $this->price;
}
Then sort the collection$products->sortBy('current_price'), but this time I want to get the query builder in the results.
Query Builder cannot use virtual properties.
I tried 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 two filters 'quantity' and 'price'.
In this multiple sort, I want to sort the products by price and then sort all the products by 'qty'. Products with qty == 0 need to follow all products with qty > 0.
please help me.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
first question
The query builder has no accessor, 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: It is recommended to sort in the database, consider that if you have
paginateon the product, it will only sort the returned page data.second question:
qty > 0AS 1,qty = 0AS 0, then sort in descending order:Sort by requested
priceSort by requested
qtySo the product will put
qty > 0beforeqty = 0, and then sort the records ofqty > 0by price, and then all Products are sorted byqty; records withqty = 0will be sorted by price, and then all products are also sorted byqty:$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')Vulnerable toSQL injection attack. Change toorderBy(DB::raw("IF(is_special=0, price, special_price)"), request('price', 'ASC'))