Level 102
Something like this.
Room::query()->whereRaw('id NOT IN (SELECT room_id FROM bookings WHERE ? BETWEEN arrival AND departure)', [$arrival])->get();
1 like
Apr 1, 2022
14
Level 8
Convert Query Builder to Eloquent
Hi, could someone kindly assist me to convert the following query to Eloquent, Kindly?
$availableRooms = DB::select("SELECT * FROM rooms WHERE id NOT IN (SELECT room_id FROM bookings WHERE '$arrival' BETWEEN arrival AND departure)");
Level 102
Or
$sub = Booking::query()->select('id')->whereRaw('? BETWEEN arrival AND departure)', [$arrival]);
Room::query()->whereNotIn('id', $sub)->get();
1 like
Level 8
thanks @sinnbeck , what if the Room has a relationship to a room type (roomTypes) can I add the with('roomTypes') before I get?
something in the likes of:
$availableRooms = Room::query()->whereRaw('id NOT IN (SELECT room_id FROM bookings WHERE ? BETWEEN arrival AND departure)', [$arrival])->with('roomtypes')->get(); ?
Level 102
@Brammah No. with() loads the relationship. It does not limit the rooms query in any way. To do that you can use whereDoesntHave()
Room::query()->whereDoesntHave('bookings', function($query) use ($arrival) {
$query->whereRaw('? BETWEEN arrival AND departure)', [$arrival]);
})->get();
or if you want both
Room::query()->whereDoesntHave('bookings', function($query) use ($arrival) {
$query->whereRaw('? BETWEEN arrival AND departure)', [$arrival]);
})->with(['bookings' => function($query) use ($arrival) {
$query->whereRaw('? BETWEEN arrival AND departure)', [$arrival]);
}])->get();
Level 8
@Sinnbeck in this case, I guess your 'rooms' is the relationship?
Level 102
@Brammah Yeah exactly. Should have been bookings.. Sorry :D Fixed
1 like
Level 8
@Sinnbeck I am getting this error:
"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 (SQL: select * from rooms where not exists (select * from bookings where rooms.id = bookings.room_id and 2022-02-28 BETWEEN arrival AND departure)))"
Level 102
I suggest checking the query being generated, if it does not work, and then test it in your database editor to see if you can post the mistake. Or post the raw sql here.
dd(Room::query()->whereDoesntHave('bookings', function($query) use ($arrival) {
$query->whereRaw('? BETWEEN arrival AND departure)', [$arrival]);
})->toSql());
1 like
Level 8
@Sinnbeck I have used one of your suggestions and I have seen it work, see below:
public function searchRoom(Request $request) { $availableRooms = null; if ($request->filled(['arrival', 'departure'])) { $times = [ Carbon::parse($request->input('arrival')), Carbon::parse($request->input('departure')), ];
$availableRooms = Room::whereDoesntHave('bookings', function ($query) use ($times) {
$query->whereBetween('arrival', $times)
->orWhereBetween('departure', $times)
->orWhere(function ($query) use ($times) {
$query->where('arrival', '<', $times[0])
->where('departure', '<', $times[1]);
});
})->get();
}
return view('rooms.search', compact('availableRooms'));
}
Level 8
@Sinnbeck I would like to express my utmost gratitude to you. Thank you.
Level 102
@Brammah Happy to help. Sorry I couldnt read the last code you posted, as the formatting is off :)
1 like
Level 8
@Sinnbeck I am still learning how to place my codes on the markdown preview, no biggie ;)
Level 102
@Brammah The trick is LINEBREAK ``` LINEBREAK CODE LINEBREAK ````
1 like
Please or to participate in this conversation.