#laravel #tricks #eloquent

Approach to Handle Complex queries in laravel

Table Of Content


  1. Step one (Creating required directory)
  2. Step Two (Adding helper method)
  3. Step Three (Calling the query)

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.

Most Viewed Posts


Must Read


January 28, 2022

#AppImage #linux

How to Add AppImage Application to Menu in Ubuntu (Linux)

AppImages are the most easiest to run compared.The challenging task is to make the AppImage accessible globally through he system like an installed application (in the menu)

Reading time: less than a minute
How to Add Laravel Pint to VS Code Formatter
June 24, 2022

#code-quality #vscode #laravel-pint #laravel

How to Add Laravel Pint to VS Code Formatter

I have been using the unsatisfactory formatters from a long time. All of them have one thing but lack other. Laravel Pint is one who has it all, combining it with vscode is like proving you laravel project a super power.

Reading time: 2 minutes
March 24, 2021

#express js #node #node js #sequelize #mysql

How to create model association in Sequelize - express JS / Node JS

In the very initial days of my work in Node JS if faced this issue. Though Sequelize provide a great ORM for MySQL but the association within the models is a bit tricky.

Reading time: 2 minutes
December 14, 2021

#database #javascript #sequelize #npx

Sequelize CLI Commands

list of Sequelize CLI commands which comes handy, while working on a project.

Reading time: 1 minute
How to create a progressive web app (PWA) in laravel 5.* and above
July 07, 2019

#functionality #pwa #javascript #laravel

How to create a progressive web app (PWA) in laravel 5.* and above

The progressive web app is the new trend as well as the need for the present time. I hope before getting in the “HOW” part you know what is exactly a PWA

Reading time: 4 minutes
How to Add Laravel Pint to VS Code Formatter
June 24, 2022

#code-quality #vscode #laravel-pint #laravel

How to Add Laravel Pint to VS Code Formatter

I have been using the unsatisfactory formatters from a long time. All of them have one thing but lack other. Laravel Pint is one who has it all, combining it with vscode is like proving you laravel project a super power.

Reading time: 2 minutes
How to create a progressive web app (PWA) in laravel 5.* and above
July 07, 2019

#functionality #pwa #javascript #laravel

How to create a progressive web app (PWA) in laravel 5.* and above

The progressive web app is the new trend as well as the need for the present time. I hope before getting in the “HOW” part you know what is exactly a PWA

Reading time: 4 minutes
September 06, 2019

#cli #authentication #laravel

PHP artisan make:auth in laravel 6.0

As you may have known by now that laravel 6.0 has been out. Though it's expected to have a lot of changes in the major release but in 6.0 there are not that many changes.

Reading time: less than a minute
March 02, 2020

#hosting #deployment #laravel

Checklist for Production Deployment laravel Project

below are mentioned things to be taken care of at the time of deploying a laravel project to production.

Reading time: less than a minute
December 22, 2021

#nginx #react #tricks

How to remove 404 in internal pages in React Nginx

When you make a refresh in a react app in any page apart from root, if you are facing 404 add the below line of code in your nginx configuration file.

Reading time: less than a minute
mohammed-samgan-khan

Hi, I am Samgan, I know the adds are bit annoying but i hope you understand. Talking about me, I excel at solving complex problems involving logic and step by step breakdown of the problem. Besides, to develop complex algorithms, I specialize in problem framing, systems design, and product development strategy. Sometimes I also enjoy public speaking.