I've been using laravel for about 4 years now but now I'm facing my biggest challenge yet. I'm refactoring and extending an old PHP application used to map the offices where I work. There's this huge SQL query that I need to integrate somehow into Laravel's QueryBuilder to feed an eager loaded relationship.
The flow is something like this
Building => hasMany: Floor => hasMany: Seat => hasMany: BookedSeat => belongsTo: User
where Building, Floor, Seat and BookedSeat are Eloquent models.
My huge query selects from BookedSeat the Seat reservations for the current date based on many many other conditions like if the person that booked the seat is in home office, vacation, etc.(these are stored in some other tables) and sets a property on the BookedSeat instance called Status to know if the Seat is taken or not for the current day
Now I'm trying to integrate this raw query into building a JSON hierarchy that I later send to a Vue.js application running on the front-end.
The hierarchy is something like:
{
"buildings": [
{
// properties
"floors" : [
{
//properties
"seats": [
{
//properties
"booked": [
{
"user": "some user model",
"Status": "some booked status"
}
]
},
// other seats
]
},
// other floors
]
},
//other buildings
]
}
The huge query returns an array of objects that I can then use to hydrate a BookedSeat collection with but I have no idea how I can then use this collection or use the huge query directly in order to eager load the BookedSeat for each Seat for each Floor for each Building and let the framework do the heavy lifting for me.
What I tried is building a method like the following:
public static function bookedSeatsForFloor(Relation $seatQuery, Relation $bookedQuery, Carbon $forDate)
{
$format = $forDate->format('Y-m-d H:m:i');
$bindings = $seatQuery->getBindings();
/** @var AvailableSeatsQuerySimple $availableSeats */
$availableSeats = new AvailableSeatsQuerySimple($bindings, $format); // bindings are my floor id's and I'm feeding them to my big query in order to have control over which floors to load depending on the user's rights
return DB::raw($availableSeats->getRawQuery());
}
and call it like this:
Floor::where('id', $someId)->with(['seats' => static function ($seatQuery) use ($_that) {
/**
* Add to each seat the manager and the active booking
*/
$seatQuery->with(['booked' => static function ($bookedQuery) use ($seatQuery, $_that) {
return self::bookedSeatsForFloor($seatQuery, $bookedQuery, $_that->forDate);
}, 'manager'])->orderBy('seat_cir');
}]);
But I'd need to somehow modify the $bookedQuery in the bookedSeatsForFloor method with a $bookedQuery->select('something') or $bookedQuery->setQuery('some query builder instance') but I have no idea how to convert the huge query to a Builder instance.
Thanks!
PS: I would preferably want to skip rewriting the huge query into eloquent syntax because of the complexity
ADDED DETAILS:
So, as requested, this is my raw query where I changed some of the database/table names per company policy
SET NOCOUNT ON;
DECLARE
@the_date DATETIME;
SET @the_date = (SELECT CONVERT(DATETIME, ?, 120));
SELECT seat_identifier,
user_id,
FromDate,
ToDate,
Status
FROM (
SELECT d.seat_identifier,
d.user_id,
d.FromDate,
d.ToDate,
CASE
WHEN d.Status IS NULL
THEN 0
WHEN d.Status = 2
THEN 2
WHEN d.Status != 0
THEN CASE
WHEN -- New, OnGoing Request in Main_DB_Name
ho.status = 1 -- New StatusType in Main_DB_Name
OR
ho.status = 4 -- Pending StatusType in Main_DB_Name
OR
twl.status = 1 -- New StatusType in Main_DB_Name
OR
twl.status = 4 -- Pending StatusType in Main_DB_Name
OR
li.status = 1 -- New StatusType in Main_DB_Name
OR
li.status = 4 -- Pending StatusType in Main_DB_Name
OR
ctaf.status = 1 -- New StatusType2 in Main_DB_Name
OR
ctaf.status = 2 -- Ongoing StatusType2 in Main_DB_Name
THEN
2 --> Pending seat in MyApplication
WHEN -- Approved Request in Main_DB_Name
ho.status = 2
OR
twl.status = 2
OR
li.status = 1
OR
li.status = 2
OR
ctaf.status = 1
OR
ctaf.status = 2
THEN 0 -- Free Seat MyApplication
ELSE 1 -- Taken Seat MyApplication
END
END as 'Status'
FROM (
SELECT seats.seat_identifier as seat_identifier,
c.user_id,
c.FromDate,
c.ToDate,
c.Status
FROM (
SELECT fo_bs.seat_identifier,
fo_bs.user_id,
fo_bs.FromDate,
fo_bs.ToDate,
fo_bs.Status
FROM MyApplication.another_schema.BookedSeats fo_bs
INNER JOIN MyApplication.another_schema.seats AS seats ON fo_bs.seat_identifier = seats.seat_identifier
WHERE fo_bs.FromDate <= @the_date
AND fo_bs.ToDate >= @the_date
AND fo_bs.Status IN (1, 2)
AND seats.floor_id IN (###FLOOR_IDS###) -- will replace this from php with a list of "?,?,?" depending on how many floor_ids are in the query bindings
) c
INNER JOIN MyApplication.another_schema.seats AS seats ON c.seat_identifier = seats.seat_identifier) d
LEFT JOIN (SELECT requester, status
from Main_DB_Name.schema.HOME_OFFICE
WHERE Main_DB_Name.schema.HOME_OFFICE.from_date <= @the_date
and Main_DB_Name.schema.HOME_OFFICE.to_date >= @the_date) ho ON d.user_id = ho.requester
LEFT JOIN (SELECT requester, status
from Main_DB_Name.schema.TEMPORARY_WORK_LOCATION
WHERE Main_DB_Name.schema.TEMPORARY_WORK_LOCATION.from_date <= @the_date
and Main_DB_Name.schema.TEMPORARY_WORK_LOCATION.to_date >= @the_date) twl
ON d.user_id = twl.requester
LEFT JOIN (SELECT employee, status
from Main_DB_Name.schema.LEAVE_INVOIRE
WHERE Main_DB_Name.schema.LEAVE_INVOIRE.leave_date = @the_date) li ON d.user_id = li.employee
LEFT JOIN (SELECT requester, status
from Main_DB_Name.schema.TRAVEL
WHERE Main_DB_Name.schema.TRAVEL.from_date <= @the_date
and Main_DB_Name.schema.TRAVEL.until_date >= @the_date) ctaf
ON d.user_id = ctaf.requester
) y
And my models/relationships are as following:
class Building extends Model {
/* Properties */
public function floors()
{
return $this->hasMany(Floor::class);
}
}
class Floor extends Model {
/* Properties */
public function building()
{
return $this->belongsTo(Building::class);
}
public function seats()
{
return $this->hasMany(Seat::class);
}
}
class Seat extends Model {
/* Properties */
public function floor()
{
return $this->belongsTo(Floor::class);
}
public function booked()
{
return $this->hasMany(BookedSeat::class);
}
}
class BookedSeat extends Model {
/* Properties */
public function user()
{
return $this->belongsTo(User::class);
}
public function seat()
{
return $this->belongsTo(Seat::class);
}
}
The problem is quite a difficult one. I was stuck with it trying different things for more than a week in total but couldn't find any nice way of doing it.
I ended up using @Jonas Staudenmeir's suggestion by manually mapping through all my nested relationships and then setting the corresponding booked relation on my Seat model instances from a collection obtained from using BookedSeat::hydrate() with the results from the raw query as an argument.
$availableSeats = new AvailableSeatsQuerySimple($format);
// Map through all the Buildings
$this->template['buildings'] = $this->template['buildings']
->map(static function (Building $building) use ($availableSeats) {
// Map through all the Floors in a Building
$floors = $building->floors->map(static function (Floor $floor) use ($availableSeats) {
/** @var BookedSeat|Collection $booked */
$booked = $availableSeats->execute($floor->id); // execute the raw query and get the results
if(count($booked) > 0) {
// Map through all the Seats in a Floor
$seats = $floor->seats->map(static function (Seat $seat) use ($booked) {
// Select the BookedSeat for the corresponding Seat
/** @var BookedSeat $bookedSeatForRelation */
$bookedSeatForRelation = $booked->filter(static function (BookedSeat $bookedSeat) use ($seat) {
return $bookedSeat->seat_identifier === $seat->id;
})->first();
// Attach the BookedSeat to the Seat only if the Status IS NOT 0
if($bookedSeatForRelation !== null && $bookedSeatForRelation->Status !== 0) {
return $seat->setRelation('booked', $bookedSeatForRelation);
}
return $seat->setRelation('booked', null);
});
return $floor->setRelation('seats', $seats);
}
return $floor;
});
return $building->setRelation('floors', $floors);
});