Approach To Handle Complex Queries In Laravel

I am familiar with the love of Eloquent among the laravel community but sometimes, it's better to go for raw queries. It saves you a lot of time and trouble.

Trust me, it's hard to digest but it is true.

Let's consider a case where the following query is a complex one.

select count(id) from users where admin_id = 2

Step one (Creating required directory)

Create a queries directory in storage. storage/queries, you can create additional sub-directories according to the requirement.

Step Two (Adding helper method)

Add the following method to your helper methods.

if (! function_exists('getQuery')) {
    function getQuery(string $queryName, ?array $data = []): string
    {
        $query = file_get_contents(storage_path('queries/' . $queryName . '.sql'));

        if (! empty($data)) {
            foreach ($data as $key => $value) {
                $query = str_replace($key, $value, $query);
            }
        }

        return $query;
    }
}

if (! function_exists('runQuery')) {
    function runQuery(string $queryName, ?array $data = []): array
    {
        return DB::select(getQuery($queryName, $data));
    }
}

Step Three (Calling the query)

Once the directory and helper function are in place all you have to do is create SQL query files in the storage/queries directory

say, storage/queries/admin.users.sqlcontaining the query,

select count(id) from users where admin_id = adminId

Please note, that the dynamic IDs are replaced with a variable name.

Call the query with the helper function as follows

public function adminUsers(): array
{
    return runQuery('admin.users', [
        'adminId' => auth()->id()
    ]);
}

In this way, you can get any complex query data, without going through the trouble of Eloquent.

you can also get just the SQL query by calling getQuery method.

Please feel free to give your review and feedback in the form of comments.