php - 为什么具有 distinct 和 group by 的查询不返回结果？

Question

我有这个查询来获取“城市”列等于 $search 的会议。

   $cities = Conference::
    where('city', 'LIKE', '%'.$search.'%')
    -> distinct()->get(['city']);

但我还想按会议“开始日期”列的结果排序。但这样是行不通的：

$cities = Conference::
   where('city', 'LIKE', '%'.$search.'%')
  ->distinct()->orderBy('start_date', 'asc')
  ->get(['city','start_date']);

例如，如果用户在搜索自动完成字段中引入“New”，则 distinct 不起作用，它会出现多次，例如“Newcastle”，而不是只出现一次。

你知道为什么吗？

也像这样，使用 group by，当用户引入例如“New”时没有结果出现，但它应该出现“Newcastle”：

 $cities = DB::raw("SELECT city, min(start_date) FROM conferences WHERE city LIKE '%search%' 
    GROUP BY city ORDER BY min(start_date) ASC");

Search() 方法来处理自动完成搜索：

public function search(Request $request){
    $search = $request->term;
    $conferences = Conference::where('name', 'LIKE', '%'.$search.'%')->get();

    //$cities = Conference::where('city', 'LIKE', '%'.$search.'%')->distinct()->get(['city']);
    $cities = DB::raw("SELECT city, min(start_date) FROM conferences WHERE city LIKE '%" . $search . "%'
     GROUP BY city ORDER BY min(start_date) ASC");
    $data= [];
    foreach ($conferences as $key => $value){
        $data[] = ['category'=> 'Conferences', 'value' => $value->name, 'url' => 'conference/'.$value->id.'/'.$value->slug];
    }
    foreach ($cities as $key => $value){
        $data[] = ['category'=> 'Cities', 'value' => $value->city, 'url' => 'conferences/where/city/'.$value->city];
    }
    return response($data);
}

$data带有“ ”的返回$cities = Conference::where('city', 'LIKE', '%'.$search.'%')->distinct()->get(['city']);显示：

[{category: "Cities", value: "Newcastle", url: "conferences/where/city/Newcastle"}]
0
:
{category: "Cities", value: "Newcastle", url: "conferences/where/city/Newcastle"}

用“ $cities = DB::raw("SELECT city, min(start_date) FROM conferences WHERE city LIKE '%" . $search . "%' GROUP BY city ORDER BY min(start_date) ASC");”表示：

[]
No properties

标签： phplaravel