Level 6
Nobody?
Oct 7, 2016
7
Level 6
Join incorrectly includes deleted records
Hey there, i know this isn't really an Eloquent question, because I'm doing a query using the DB::table() function. But I thought this is the most suitable place to post my question!
The Problem is a follows: I have a Laravel 4.2 project with a booking table which stores bookings of vehicles. In my application I have to make sure, that no vehicle gets booked twice during the same time. Nevertheless it can possibly occur, if two users start booking the same vehicle at the same time and finish the booking process during a certain time period.
Therefore I want to have a view with duplicate bookings. I'm doing this by joining the bookings-table with itself on the vehicle ID. Then I'm checking for time overlaps. Overlapping bookings should then be displayed in a table.
In my Booking -model I'm using Eloquents soft-deleting trait. So a overlap conflict can be resolved by changing the vehicle or deleting a booking.
Now the Problem: If a conflict booking has been deleted, the conflict is resoved, as described! But as the deleted booking does not really get removed from the Booking table, the time overlap can still be found. Because of that, the query also returns this conflict. The goal is, that both conflicting records do not show up in the table, because the conflict allready got resolved. Therefore I tried checking, that the deleted_at column of all displayed records has to be null. But that's not working! The table instead displays the single record, which hasn't been deletet. My query looks like that:
$duplicateBookings = DB::table('bookings as b1')
->join('bookings as b2', function($query) {
$query->on('b2.vehicle_id', '=', 'b1.vehicle_id');
})
->where('b1.id', '!=', 'b2.id')
->whereNull('b1.deleted_at')
->whereNull('b2.deleted_at')
->where(function($query) {
$query->whereRaw("b1.end_date >b2.start_date")
->whereRaw("b1.start_date < b2.end_date");
})
->groupBy('b2.id')
->get();
To resolve the problem, I added another condition, which checks that the deleted_at of if queried records are equal! With that, it reutrns nothing:
$duplicateBookings = DB::table('bookings as b1')
->join('bookings as b2', function($query) {
$query->on('b2.vehicle_id', '=', 'b1.vehicle_id');
})
->where('b1.id', '!=', 'b2.id')
->whereNull('b1.deleted_at')
->whereNull('b2.deleted_at')
->where('b1.deleted_at', '=', 'b2.deleted_at')
->where(function($query) {
$query->whereRaw("b1.end_date >b2.start_date")
->whereRaw("b1.start_date < b2.end_date");
})
->groupBy('b2.id')
->get();
I even did output the generated query by using the toSql() function, pasted that into my PHPMyAdmin and everything worked as expected. Only Laravel returns the wrong set of records!
Can anybody help me?
Level 54
Include a where condition in your join to omit deleted items from being joined in the first place?
Level 6
I tried that too, like so:
$duplicateBookings = DB::table('bookings as b1')
->join('bookings as b2', function($query) {
$query->on('b2.vehicle_id', '=', 'b1.vehicle_id');
->whereNull('b1.deleted_at')
->whereNull('b2.deleted_at');
})
->where('b1.id', '!=', 'b2.id')
->where(function($query) {
$query->whereRaw("b1.end_date >b2.start_date")
->whereRaw("b1.start_date < b2.end_date");
})
->groupBy('b2.id')
->get();
I also tried using a where closure:
$duplicateBookings = DB::table('bookings as b1')
->join('bookings as b2', function($query) {
$query->on('b2.vehicle_id', '=', 'b1.vehicle_id');
->where(function($query) {
$query->whereNull('b1.deleted_at')
->whereNull('b2.deleted_at');
});
})
->where('b1.id', '!=', 'b2.id')
->where(function($query) {
$query->whereRaw("b1.end_date >b2.start_date")
->whereRaw("b1.start_date < b2.end_date");
})
->groupBy('b2.id')
->get();
But that doesn't seem to be allowed inside a join, as it throws an error!
Level 54
Try this:
$duplicateBookings = DB::table('bookings as b1')
->whereNull('b1.deleted_at')
->join('bookings as b2', function($query) {
$query->on('b2.vehicle_id', '=', 'b1.vehicle_id');
$query->whereNull('b2.deleted_at');
})
->where('b1.id', '!=', 'b2.id')
->where(function($query) {
$query->whereRaw("b1.end_date > b2.start_date")
->whereRaw("b1.start_date < b2.end_date");
})
->groupBy('b2.id')
->get();
1 like
Level 6
This also didn't work!
Level 122
Would it simplify matters if you checked for double booking immediately after any vehicle booking, since at booking or cancellation are the only times you could end up with a clash?
There does not seem to be much point in checking every vehicle every time if they were ok when booked?
1 like
Level 6
Sadly, this isn't a decision I can make, as it is a mechanism my client wants. It would be too much to explain the arguments here, but for now i have to deal with the above described problem. I would be happy for every other idea for solving it!
Please or to participate in this conversation.