I took the bull by it's horns. Spoiler: I didn't die
Tags: backend, laravel
Published: 2024-06-27 14:47:59

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.

```php
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](https://hng.tech/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](https://linkedin.com/in/adetimehin), 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](https://hng.tech/hire)

Till I write again...