To rewrite the given query using Eloquent or DB::select(DB::raw()), you can follow these steps:
-
Create a new model for the
Propertytable and a new model for theBookingtable if you haven't already done so. -
In the model for the
Propertytable, define a relationship with theBookingmodel. Assuming the relationship is one-to-many, you can use thehasManymethod. For example:
// Property.php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Property extends Model
{
public function bookings()
{
return $this->hasMany(Booking::class);
}
}
- In the model for the
Bookingtable, define a relationship with thePropertymodel. Assuming the relationship is many-to-one, you can use thebelongsTomethod. For example:
// Booking.php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Booking extends Model
{
public function property()
{
return $this->belongsTo(Property::class);
}
}
- Now, you can rewrite the query using Eloquent. Here's an example of how you can do it:
$query = Property::selectRaw('
v.selected_date,
properties.spaces - COALESCE(SUM(bookings.spaces), 0) AS available,
SUM(CASE WHEN DATE(bookings.checkout) = v.selected_date THEN bookings.spaces ELSE 0 END) AS checkout
')
->fromSub(function ($query) use ($property_id, $start_date, $end_date) {
$query->selectRaw('ADDDATE("1970-01-01", t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS selected_date')
->from('t0')
->crossJoin('t1')
->crossJoin('t2')
->crossJoin('t3')
->crossJoin('t4');
}, 'v')
->leftJoin('properties', 'properties.id', '=', $property_id)
->leftJoin('bookings', function ($join) use ($property_id) {
$join->on('bookings.property_id', '=', $property_id)
->where('bookings.status', '=', 1)
->whereRaw('DATE(v.selected_date) BETWEEN DATE(bookings.checkin) AND DATE(bookings.checkout)');
})
->whereBetween('v.selected_date', [$start_date, $end_date])
->groupBy('v.selected_date', 'properties.spaces')
->get();
Note: Make sure to replace Property and Booking with the actual names of your models.
This rewritten query uses Eloquent's query builder methods and takes advantage of the relationships defined in the models. The selectRaw method is used to specify the raw SQL expressions. The fromSub method is used to create a subquery for generating the date range. The leftJoin method is used to perform the left join operations. Finally, the whereBetween method is used to filter the selected dates.
Remember to adjust the code according to your specific database structure and requirements.
