如何在 Laravel 中获得最近的行？

Question

我有一张带有价格列的表格。现在我想按价格获得最近的行。喜欢：如果我给值 1000 需要返回 3,5,6 没有。行。，如果我给值 100 需要返回 1,2 没有。排

打包表演示

id - name - provider_id - price - others_field

1  - name -    1        -  10

2  - name -    1        -  120

3  - name -    1        -  600

4  - name -    1        -  320

5  - name -    1        -  1000

6  - 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,

];

Answer 1

获取类似于 $price 的最大价格：SELECT&nbsp;*&nbsp;FROM&nbsp;tab&nbsp;WHERE&nbsp;val&nbsp;<=&nbsp;$price&nbsp;ORDER&nbsp;BY&nbsp;val&nbsp;DESC&nbsp;LIMIT&nbsp;3获取类似于 $val 的最小价格：SELECT&nbsp;*&nbsp;FROM&nbsp;tab&nbsp;WHERE&nbsp;val&nbsp;>=&nbsp;$price&nbsp;ORDER&nbsp;BY&nbsp;val&nbsp;LIMIT&nbsp;3在任一方向获取与 $price 相似的最接近价格：SELECT&nbsp;*&nbsp;FROM&nbsp;tab&nbsp;ORDER&nbsp;BY&nbsp;abs(val&nbsp;-&nbsp;$val)&nbsp;LIMIT&nbsp;3

Answer 2

试试这个查询。您将从数据库中获得近 5 条记录SELECT *&nbsp;FROM( ( SELECT *&nbsp; &nbsp; FROM pack&nbsp; &nbsp; WHERE price <= 1000&nbsp; &nbsp; ORDER BY price DESC&nbsp; &nbsp; &nbsp; LIMIT 3&nbsp; )&nbsp;&nbsp; UNION ALL&nbsp; ( SELECT *&nbsp; &nbsp; FROM pack&nbsp; &nbsp; WHERE price > 1000&nbsp;&nbsp; &nbsp; ORDER BY price ASC&nbsp; &nbsp; &nbsp; LIMIT 3&nbsp; )&nbsp;) AS tmpORDER BY priceLIMIT 5 ;

Answer 3

你能检查你的代码的结果查询，并将其粘贴到这里吗？我怀疑问题出在这一行$suggestions&nbsp;=&nbsp;$suggestions->select('*',&nbsp;DB::raw("ABS(price&nbsp;-&nbsp;$request->price)&nbsp;AS&nbsp;distance"))->orderBy('distance');where$request->price被视为未替换为您期望的值的字符串。