我有一張帶有價格列的表格。現在我想按價格獲得最近的行。喜歡：如果我給值 1000 需要返回 3,5,6 沒有。行。，如果我給值 100 需要返回 1,2 沒有。排打包表演示id - name - provider_id - price - others_field1 - name - 1 - 102 - name - 1 - 1203 - name - 1 - 6004 - name - 1 - 3205 - name - 1 - 10006 - name - 1 - 1020我通過搜索谷歌嘗試了以下方式。但它不能正常工作。$suggestions = new Pack; $suggestions = $suggestions->where('status', 1); $suggestions = $suggestions->where('provider_id', $request->provider_id); $suggestions = $suggestions->select('*', DB::raw("ABS(price - $request->price) AS distance"))->orderBy('distance'); $suggestions = $suggestions->take(3); $suggestions = $suggestions->get(); $curent = '';$suggestion_lower = '';$suggestion_higher = '';foreach ($suggestions as $value) { if ( $request->price == $value->price ) { $curent = $value; } if ( $request->price > $value->price ) { $suggestion_lower = $value; } if ( $request->price < $value->price ) { $suggestion_higher = $value; }}$suggestions = [ 'curent' => $curent, 'suggestion_higher' => $suggestion_higher, 'suggestion_lower' => $suggestion_lower,];
3 回答
慕萊塢森
TA貢獻1810條經驗 獲得超4個贊
獲取類似于 $price 的最大價格：
SELECT * FROM tab WHERE val <= $price ORDER BY val DESC LIMIT 3
獲取類似于 $val 的最小價格：
SELECT * FROM tab WHERE val >= $price ORDER BY val LIMIT 3
在任一方向獲取與 $price 相似的最接近價格：
SELECT * FROM tab ORDER BY abs(val - $val) LIMIT 3
蠱毒傳說
TA貢獻1895條經驗 獲得超3個贊
試試這個查詢。您將從數據庫中獲得近 5 條記錄
SELECT *
FROM
( ( SELECT *
FROM pack
WHERE price <= 1000
ORDER BY price DESC
LIMIT 3
)
UNION ALL
( SELECT *
FROM pack
WHERE price > 1000
ORDER BY price ASC
LIMIT 3
)
) AS tmp
ORDER BY price
LIMIT 5 ;
30秒到達戰場
TA貢獻1828條經驗 獲得超6個贊
你能檢查你的代碼的結果查詢，并將其粘貼到這里嗎？
我懷疑問題出在這一行
$suggestions = $suggestions->select('*', DB::raw("ABS(price - $request->price) AS distance"))->orderBy('distance');where$request->price被視為未替換為您期望的值的字符串。
- 3 回答
- 0 關注
- 133 瀏覽
添加回答
舉報
0/150
提交
取消