I took the bull by it's horns. Spoiler: I didn't die

As I prepare to embark on an exciting journey with the HNG 11 Internship, again 🥲, I’m reminded of a particularly challenging sql querying problem I recently encountered and successfully resolved. This experience really helped me see things in a more different light than the usual.

P.S: This project is written in laravel so i might be very laravel specific.

Yeah, PHP is not dead, yet😂

The Problem

The issue came while i was building a ride booking software to be used by transport businesses. I needed to write a query to filter available rides by sent in parameters. Now you might think, ohh, this is straightforward, yeah it is, or i thought it was, if only the database schema was straightforward.

Picture this, I had a Ride, RideInstance, and RideDestinationLocation Eloquent model, I had more, but these are most important to the scope of this article. In essence, this was the optimal design (Let me know if you think this can be better) to create recurring rides for each day, and each ride could have multiple stop destinations.

Now the issue came because users don't book Ride(this is only a template) but the RideInstance, which is only be aware of destination locations through the Ride model.

The Solution

Talk is cheap, let's get into the code.

return RideInstance::query()
            ->with($relationships)
            ->withCount([
                'seats as available_seats' => function (Builder $query) {
                    $query->whereNull('booked_at');
                },
            ])
            ->when($departureCityId, function (Builder $query) use ($departureCityId) {
                $query->whereHas(
                    'ride.departureLocation.city',
                    function (Builder $query) use ($departureCityId) {
                        $query->where('id', $departureCityId);
                    },
                );
            })
            ->when($destinationCityId, function (Builder $query) use ($destinationCityId) {
                $query->whereHas(
                    'ride.rideDestinationLocations.city',
                    function (Builder $query) use ($destinationCityId) {
                        $query->where('id', $destinationCityId);
                    },
                );
            })
            ->when($departureDate, function (Builder $query) use ($departureDate) {
                $query->whereDate('departure_date', $departureDate);
            })
            ->when($businessId, function (Builder $query) use ($businessId) {
                $query->whereHas('ride.business', function ($query) use ($businessId) {
                    $query->where('id', $businessId);
                });
            })
            ->paginate($perPage);
    }

Could this be improved??

Definitely, I very much intended to improve this, maybe implement this using manual sql queries instead of Eloquent ORM (Bro can be non performant at times), to gain a bit of latency, but that's if and when i encounter actual issues.

HNG 11 Journey

As I look forward to starting the HNG Internship, I’m excited about the opportunities to further hone my skills and tackle new challenges. The internship promises a dynamic environment where I can collaborate with other talented interns, learn from real-world projects, and contribute to innovative solutions.

My passion for building solutions that solve real problems and (my fear of the trenches😭) drives me to continuously improve and adapt. The HNG 11 Internship is a perfect platform to achieve these goals, offering a blend of practical experience and mentorship. Hopefully I get past stage 5 this time though😂, I’m eager to embark on this journey, ready to face new challenges, learn, and grow as a developer.

Perhaps you're a recruiter seeing this, consider reaching out to me here, if you think I'm not fit (which is probably not true, reach out first), then you'll definitely find brilliant minds at HNG Talents

Till I write again...