mysql - 根据 whereHas 查询获取最高和最低数据

Question

基于公寓查找器，显示/返回所有具有公寓的类型，基于表单搜索。这是使用 whereHas 查询完成的。

例如:

A user has different choices; a garden/balcony, price range, floor etc.

The query is being run based on these choices, and if there are apartments which meet the search requirements the parent of these apartments (type) are being returned.

最终我想显示一个类型的living surface范围(lowest-highest)，但是每个living surface是不同的公寓。

获得 highest and lowest apartment.living_surface 返回的最有效方法是什么whereHas。

$types = Type::where('type', '=', 'studio')->whereHas('apartments', function ($query) use ($request) {
    if ($request->view == 'garden') {
        $query->where('view', '=', $request->view);
    } elseif ($request->view == 'balcony') {
        $query->where('view', '=', $request->view);
    } elseif ($request->view == 'other') {
        $query->where('view', '=', $request->view);
    }

    if ($request->floor == 'upper') {
        $query->where('floor', '<', '5');
    } elseif ($request->floor == 'lower') {
        $query->where('floor', '>', '4');
    }

    if ($request->sun == 'morning') {
        $query->where('sun', '=', 'morning');
    } elseif ($request->sun == 'evening') {
        $query->where('sun', '=', 'evening');
    }

    if ($request->price == '1') {
        $query->where('price', '<', '900');
    } elseif ($request->price == '2') {
        $query->where('price', '<', '999');
    } elseif ($request->price == '3') {
        $query->where('price', '>', '999');
    }
})->with('apartments')->get();

一个解决方案是(我认为)是提取 apartments 的所有 ids 并根据 ids 运行另一个查询，但是由于运行多个查询(我希望)在一个查询中完成某些事情，所以这实际上并没有尽可能优化。

Answer 1

这可以通过 Collection Methods - Laravel Docs 来完成

可以在查询的集合上运行方法 - 在这种情况下 $types

foreach ($types as $type) {
    $type->lowest_living_area = $type->apartments>sortBy('living_area')->first()->living_area);
    $type->highest_living_area = $type->apartments->sortBy('living_area')->last()->living_area);
}