Laravel技术实现：在关联查询中获取房间表的最低价格

Hey there! To fetch the minimum price from the room table for each associated hotel in your query, you can modify the eager loading closure to include an aggregate calculation. Here's how to do it properly:

Modified Query #

$greatDeals = Deal::whereHas('hotel', function ($query) {
    $query->whereHas('room', function ($query) {
        $query->where('astatus', 1)->where('status', 0);
    })->where('astatus', 1)->where('status', 0);
})
->with(['hotel' => function ($query) {
    // Add the minimum room price as a custom attribute to the hotel
    $query->selectRaw('hotels.*, (SELECT MIN(price) FROM rooms WHERE rooms.hotel_id = hotels.id AND rooms.astatus = 1 AND rooms.status = 0) as min_room_price')
          // Optional: Keep loading rooms if you need them (filtered to available ones)
          ->with(['room' => function ($roomQuery) {
              $roomQuery->where('astatus', 1)->where('status', 0);
          }]);
}])
->where('date', '>=', Carbon::today()->toDateString())
->where('status', 1)
->orderBy('discount', 'desc')
// Add your limit/offset here if needed (e.g., ->limit(10))
->get();

Key Changes Explained #

• Correlated Subquery: The selectRaw clause uses a subquery to calculate the minimum price only for rooms that match your availability conditions (astatus = 1 and status = 0), ensuring you get the lowest price for active, available rooms.
• Custom Attribute: Each hotel instance will now have a min_room_price attribute you can access directly (e.g., $deal->hotel->min_room_price).
• Optional Room Loading: If you don't need to fetch all the rooms for each hotel, you can remove the inner ->with('room') part to optimize performance.

Alternative Approach: Using Join & GroupBy #

If you prefer a join-based method (which can be more efficient in some scenarios), here's another version:

$greatDeals = Deal::whereHas('hotel', function ($query) {
    $query->whereHas('room', function ($query) {
        $query->where('astatus', 1)->where('status', 0);
    })->where('astatus', 1)->where('status', 0);
})
->with(['hotel' => function ($query) {
    $query->join('rooms', 'hotels.id', '=', 'rooms.hotel_id')
          ->selectRaw('hotels.*, MIN(rooms.price) as min_room_price')
          ->where('rooms.astatus', 1)
          ->where('rooms.status', 0)
          ->groupBy('hotels.id')
          // Optional: Load rooms if needed
          ->with(['room' => function ($roomQuery) {
              $roomQuery->where('astatus', 1)->where('status', 0);
          }]);
}])
->where('date', '>=', Carbon::today()->toDateString())
->where('status', 1)
->orderBy('discount', 'desc')
->get();

Note: Ensure your database table names (hotels, rooms) match what's used in the query, and adjust column names if your price column has a different name.

内容的提问来源于stack exchange，提问作者arun