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

Question

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.

mrobbizulfikar · Accepted Answer · 2022-06-14 04:12:55Z

1

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();

answered Jun 14, 2022 at 4:12

mrobbizulfikar

4615 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Misha Over a year ago

Thank you, this one solved my problem. But why when I try to use paginate(), it returns an empty array on the next page?

Huy Dev · Accepted Answer · 2022-06-14 04:21:42Z

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']);
                        });
                    });
            });

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related