README

使用 openAI API 优化 Laravel 数据库或 Eloquent 读取查询，并获得关于如何使您的查询更快的见解

安装

首先，使用 composer 安装包

composer require jumamiller/optimize-laravel-query --dev

然后，发布 openAI 配置文件

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

最后，将 openAPI 密钥添加到您的 .env 文件中

OPENAI_API_KEY=sk-...

到此为止，您就准备好使用了

用法

此包可以帮助您使用 openAI 优化 Eloquent 或 DB 查询

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();

包具有以下方法

optimize()

此方法负责优化查询并返回 Optimizer 类的实例。

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` = ?

toSql()

此方法将返回优化查询的字符串形式

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` = ?

get()

此方法将执行由 optimize() 方法生成的优化查询并返回结果

⚠️ 在使用此方法之前，请确保您知道您正在运行的查询，因为它将执行优化后的查询而不是原始查询

    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']

explain()

此方法将返回一个键值数组，包含优化查询、执行此类优化背后的推理以及进一步手动优化查询的建议

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
]

致谢

Miller Juma

许可协议

MIT 许可协议 (MIT)。请参阅 许可文件 了解更多信息。

灵感来源