PHP code example of omaresmael / laravel-query-optimizer

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

omaresmael / laravel-query-optimizer example snippets

User::query() //can be applied to `DB facade` as well (see tests)
    ->select('users.id', 'users.name')
    ->whereHas('roles', function ($query) {
        $query->where('name', 'author');
    })
    ->whereHas('posts', function ($query) {
        $query->where('title', 'Awesome post');
    })->optimize()->get();

User::query() 
   ->select('users.id', 'users.name')
    ->whereHas('roles', function ($query) {
        $query->where('name', 'author');
    })
    ->whereHas('posts', function ($query) {
        $query->where('title', 'Awesome post');
    })->optimize();

//old query => select `id`, `name` from `users` where exists (select * from `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` where `users`.`id` = `role_user`.`user_id` and `type` = ?) and exists (select * from `posts` where `users`.`id` = `posts`.`user_id` and `title` = ?)

//optimized query => SELECT `id`, `name` FROM `users` INNER JOIN `role_user` ON `users`.`id` = `role_user`.`user_id` INNER JOIN `roles` ON `roles`.`id` = `role_user`.`role_id` INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id` WHERE `type` = ? AND `title` = ?

User::query()
    ->select('users.id', 'users.name')
    ->whereHas('roles', function ($query) {
        $query->where('type', 'author');
    })
    ->whereHas('posts', function ($query) {
        $query->where('title', 'Awesome post');
    })->optimize()->toSql();
    
//output => SELECT `id`, `name` FROM `users` INNER JOIN `role_user` ON `users`.`id` = `role_user`.`user_id` INNER JOIN `roles` ON `roles`.`id` = `role_user`.`role_id` INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id` WHERE `type` = ? AND `title` = ?

    User::query()
    ->select('users.id', 'users.name')
    ->whereHas('roles', function ($query) {
        $query->where('type', 'author');
    })
    ->whereHas('posts', function ($query) {
        $query->where('title', 'Awesome post');
    })->optimize()->get()->toArray();
    
    //output => ['id' => '1', 'name' => 'omar']

User::query()
    ->select('users.id', 'users.name')
    ->whereHas('roles', function ($query) {
        $query->where('type', 'author');
    })
    ->whereHas('posts', function ($query) {
        $query->where('title', 'Awesome post');
    })->optimize()->explain();

[
'optimizedQuery' => 'SELECT `id`, `name` FROM `users` INNER JOIN `role_user` ON `users`.`id` = `role_user`.`user_id` INNER JOIN `roles` ON `roles`.`id` = `role_user`.`role_id` INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id` WHERE `type` = ? AND `title` = ?' // the optimized query
'reasoning'      => 'This query optimizes the original query by using JOINs to reduce the number of subqueries and improve the performance of the query. By using JOINs, the query can access the data from multiple tables in a single query, instead of having to make multiple subqueries.' //the reasoning behind performing such optimization,
'suggestions'    => 'It may be beneficial to add an index on the `type` and `title` columns to further improve the performance of the query.' //suggestions to manually optimize the query even further
]

bash
php artisan vendor:publish --provider="OpenAI\Laravel\ServiceProvider"