Approach to Handle Complex queries in laravel

November 02, 2021 ~ Mohammed Samgan Khan ~ 328 views

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

Trust me, its hard to digest but is true.

Lets 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 further sub directories as per requirement.

Step Two (Adding helper method)

Add the following method to you helper methods.

if (!function_exists('getQuery')) {
    /**
     * @param $queryName
     * @param null $data
     * @return array|false|string|string[]
     */
    function getQuery($queryName, $data = null)
    {
        $query = file_get_contents(storage_path('queries/' . $queryName . '.sql'));

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

        return $query;
    }
}

Step Three (Calling the query)

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

say, storage/queries/admin.users.sql

containing the query,

select count(id) from users where admin_id = adminId

Please make note, the dynamic ids are replaced with a variable name.

Call the query with helper function as follows.

use Illuminate\Support\Facades\DB;

/**
 * @return array
 */
public function adminUsers(): array
{
    return DB::select(getQuery('admin.users', [
        'adminId' => auth()->id()
    ]));
}

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

Feel free to provide us with your review and feedback in the form of comments.