Join 2 tables in a Single Eloquent Laravel using multiple where clause-CodePudding

here I'd like to find the solution to simplify my query to get data using eloquent in Laravel.

$room_id = Booking::whereBetween('from', [$request->from, $request->to])
                     ->orWhereBetween('to', [$request->from, $request->to])
                     ->where('from', '<=', $request->from, )
                     ->where('to', '>=', $request->from)
                     ->pluck('room_id');
                     
$rooms = Room::whereNotIn('id', $room_id )->get();

So here I have 2 Eloquent operations to get Rooms which not included in the Booking Table with specified requirements. So far I have no problem with it, but can you guys give me best practice to simplify from what I do? Thank you.

CodePudding user response：

Make sure that 'bookings' relation is written on your Room model.

$rooms = Room::whereDoesntHave('bookings', use($request) function($q){ 
    $q->whereBetween('from', [$request->from, $request->to])
    $q->orWhereBetween('to', [$request->from, $request->to])
    $q->where('from', '<=', $request->from, )
    $q->where('to', '>=', $request->from)
})->get();

CodePudding user response：

Your can refer laravel relationship to add it in model and after that using whereHas to query join table: https://laravel.com/docs/9.x/eloquent-relationships

Example:

With options

protected $with = [ 'product_savour' ];

Relationship

public function product_savour() { return $this->hasMany(ProductSavour::class, 'product_id'); }

Query

$productQuery->whereHas('product_savour', function ($query) use ($filters) {
                    $query->whereHas('savour', function ($query) use ($filters) {
                        $query->whereHas('type', function ($query) use ($filters) {
                            $query->whereIn('id', $filters['savour']);
                        });
                    });
            });

CodePudding user response：

You don't need to do a join query since you already have room_id which is also stored in bookings table. We also don't need to check the check out date of the room. if the room is check-in between the user given date range, that means room is not available. So just check the check-in date. I assume check-in and check out are stored as date in your database.

$check_in = date($request->from);
$check_out = date($request->to);
$booking=DB::table('bookings')
            ->where('room_id',$id)
            ->whereBetween('from',[$check_in,$check_out])
            ->get();