php - 使用嵌套关系的结果查询关系

在这里，我有下面的代码，我想过滤我的酒店并为每个酒店选择价格最低的房间：

 $city_id = $request->get('city_id');
            $stars = $request->get('grade_stars');
            $type_id = $request->get('accommodation_type_id');
            $from_date = $request->get('from_date');
            $to_date = $request->get('to_date');
            $data = Accommodation::with(['city','accommodationRoomsLimited.roomPricingHistorySearch' =>function($query) use($from_date,$to_date){
                $query->whereDate('from_date', '<=', $from_date);
                $query->whereDate('to_date', '>=', $to_date);
                }])
                ->whereIn('city_id',$city_id)
                ->whereIn('grade_stars',$stars)
                ->orWhere('accommodation_type_id',$type_id)
                ->paginate(10);
            return $data;

这是我的住宿模式：

    public function accommodationRoomsLimited()
{
    return $this->Hasmany(AccommodationRoom::class)->select('id', 'accommodation_id');
}

在我的住宿房型中：

  public function roomPricingHistorySearch()
{
    return $this->hasOne(RoomPricingHistory::class,'accommodation_room_id','id')->orderBy('sales_price','ASC');
}

我有一个名为“房间价格历史记录”的表，该表保留了每天的房间价格日志，这是我在api中的输出，我希望在room_pricing_history_search中选择sales_price最低的1个房间：

"accommodation_rooms_limited": [
        {
            "id": 4859,
            "accommodation_id": 47,
            "room_pricing_history_search": {
                "id": 15741,
                "accommodation_room_id": 4859,
                "net_price": null,
                "sales_price": 50,

                "created_at": "2019-05-25 13:30:00",
                "updated_at": "2019-05-25 13:30:00"
            }
        },
        {
            "id": 4860,
            "accommodation_id": 47,
            "room_pricing_history_search": {
                "id": 4990,
                "accommodation_room_id": 4860,
                "net_price": null,
                "sales_price": 1760000,

                "created_at": "2019-05-25 13:30:00",
                "updated_at": "2019-05-25 13:30:00"
            }
        },
        {
            "id": 4861,
            "accommodation_id": 47,
            "room_pricing_history_search": {
                "id": 4991,
                "accommodation_room_id": 4861,
                "net_price": null,
                "sales_price": 2270000,

                "created_at": "2019-05-25 13:30:00",
                "updated_at": "2019-05-25 13:30:00"
            }
        },
        {
            "id": 4862,
            "accommodation_id": 47,
            "room_pricing_history_search": {
                "id": 4992,
                "accommodation_room_id": 4862,
                "net_price": null,
                "sales_price": 2780000,

                "created_at": "2019-05-25 13:30:00",
                "updated_at": "2019-05-25 13:30:00"
            }
        }
    ]
},

最佳答案

首先选择房间而不是旅馆，然后按旅馆分组可能会更简单。

您可以使用raw expression来获得最低的房价：

$raw = DB::raw('min(price) as min_price');

然后从“房间”表中选择并按酒店ID进行分组：

Room::join('hotels', 'hotels.id' ,'=', 'rooms.hotel_id')
   ->whereDate('rooms.from_date', '<=', $from_date)
   ->whereDate('rooms.to_date', '>=', $to_date)
   ->select('hotels.*', $raw)
   ->groupBy('hotels.id')
   ->get()

乔纳森·赖因克（Jonathan Reinink）最近的演讲Eloquent Performance Patterns也可能引起您的兴趣。他谈到了与您类似的查询，并且看起来像他谈到的拉取请求使Laravel 6.0中的merged变得更容易了。因此，从Laravel 6.0开始，您应该可以执行以下操作：

return Hotel::addSelect(['lowest_price' => function ($query) {
    $query->select('price')
        ->from('rooms')
        ->whereColumn('hotel_id', 'hotels.id')
        ->orderBy('price', 'asc')
        ->limit(1);
}])->get();

关于php - 使用嵌套关系的结果查询关系，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57758130/