将自定义函数添加到 Laravel 查询生成器

我正在尝试添加USE INDEX()到 Laravel 中的查询生成器。我尝试遵循类似的步骤link https://medium.com/@justin.park001/laravel-techniques-extending-the-query-builder-2f4aca7956a2并取得了一定的成功，但我无法管理最后一点，而且我不确定我的临时代码是否创建了一个巨大的后门。

目标：我练习的目标是将索引添加到查询生成器，如下所示：

DB::table('users')->where('id',1)->**useIndex**('users')->get()->first();

这里有一个选项useIndex指定我将用于此查询的索引。

我已经做了什么：创建了一个名为Connection in App/Override

   <?php
    
    namespace App\Override;
    class Connection extends \Illuminate\Database\MySqlConnection {
        //@Override
        public function query() {
            return new QueryBuilder(
                $this,
                $this->getQueryGrammar(),
                $this->getPostProcessor()
            );
        }
    }

创建了一个名为CustomDatabaseServiceProvider在应用程序/提供商中。这里我只是操纵了registerConnectionServices功能。我进一步评论Illuminate\Database\DatabaseServiceProvider::class,并添加了 App\Providers\CustomDatabaseServiceProvider::class, to app.php在配置目录中。

<?php

namespace App\Providers;

use App\Override\Connection;
use Illuminate\Database\DatabaseManager;
use Illuminate\Database\Query\Grammars\Grammar;
use Illuminate\Database\Schema;
use Illuminate\Contracts\Queue\EntityResolver;
use Illuminate\Database\Connectors\ConnectionFactory;
use Illuminate\Database\Eloquent\Factory as EloquentFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\QueueEntityResolver;
use Illuminate\Support\ServiceProvider;

class CustomDatabaseServiceProvider extends ServiceProvider
{
    /**
     * The array of resolved Faker instances.
     *
     * @var array
     */
    protected static $fakers = [];

    /**
     * Bootstrap the application events.
     *
     * @return void
     */
    public function boot()
    {
        Model::setConnectionResolver($this->app['db']);

        Model::setEventDispatcher($this->app['events']);
    }

    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        Model::clearBootedModels();

        $this->registerConnectionServices();

        $this->registerEloquentFactory();

        $this->registerQueueableEntityResolver();
    }

    /**
     * Register the primary database bindings.
     *
     * @return void
     */
    protected function registerConnectionServices()
    {
        // The connection factory is used to create the actual connection instances on
        // the database. We will inject the factory into the manager so that it may
        // make the connections while they are actually needed and not of before.
        $this->app->singleton('db.factory', function ($app) {
            return new ConnectionFactory($app);
        });

        // The database manager is used to resolve various connections, since multiple
        // connections might be managed. It also implements the connection resolver
        // interface which may be used by other components requiring connections.
        $this->app->singleton('db', function ($app) {
            $dbm = new DatabaseManager($app, $app['db.factory']);
            //Extend to include the custom connection (MySql in this example)
            $dbm->extend('mysql', function ($config, $name) use ($app) {
                //Create default connection from factory
                $connection = $app['db.factory']->make($config, $name);
                //Instantiate our connection with the default connection data
                $new_connection = new Connection(
                    $connection->getPdo(),
                    $connection->getDatabaseName(),
                    $connection->getTablePrefix(),
                    $config
                );
                //Set the appropriate grammar object
//                $new_connection->setQueryGrammar(new Grammar());
//                $new_connection->setSchemaGrammar(new Schema\());
                return $new_connection;
            });
            return $dbm;
        });

        $this->app->bind('db.connection', function ($app) {
            return $app['db']->connection();
        });
    }

    /**
     * Register the Eloquent factory instance in the container.
     *
     * @return void
     */
    protected function registerEloquentFactory()
    {
        $this->app->singleton(FakerGenerator::class, function ($app, $parameters) {
            $locale = $parameters['locale'] ?? $app['config']->get('app.faker_locale', 'en_US');

            if (!isset(static::$fakers[$locale])) {
                static::$fakers[$locale] = FakerFactory::create($locale);
            }

            static::$fakers[$locale]->unique(true);

            return static::$fakers[$locale];
        });

        $this->app->singleton(EloquentFactory::class, function ($app) {
            return EloquentFactory::construct(
                $app->make(FakerGenerator::class), $this->app->databasePath('factories')
            );
        });
    }

    /**
     * Register the queueable entity resolver implementation.
     *
     * @return void
     */
    protected function registerQueueableEntityResolver()
    {
        $this->app->singleton(EntityResolver::class, function () {
            return new QueueEntityResolver;
        });
    }
}

最后创建了一个名为QueryBuilder在应用程序/覆盖中。这是有问题的类：

<?php

namespace App\Override;

use Illuminate\Support\Facades\Cache;

class QueryBuilder extends \Illuminate\Database\Query\Builder
{
    private $Index = [];

    public function useIndex($index = null)
    {
        $this->Index = $index;
        return $this;
    }

    //@Override
    public function get($columns = ['*'])
    {
        if ($this->Index) {
            //Get the raw query string with the PDO bindings
            $sql_str = str_replace('from `' . $this->from . '`', 'from `' . $this->from . '` USE INDEX (`' . $this->Index . '`) ', $this->toSql());
            $sql_str = vsprintf($sql_str, $this->getBindings());
            return parent::get($sql_str);
        } else {
            //Return default
            return parent::get($columns);
        }
    }
}

这里的问题是：

1. 输出不包含 USE INDEX
2. 使用 str_replace 操作查询安全吗？

查询生成器是可宏的，因此在您的服务提供商中您可以执行以下操作：

Illuminate\Database\Query\Builder::macro(
    'tableWithIndex',
    function ($table, $index) {
        $table = $this->grammar->wrapTable($table);
        $index = $this->grammar->wrap($index);
        return $this->fromRaw("$table USE INDEX ($index)");
    }
);

然后你可以使用这个：

DB::tableWithIndex('users', 'users');

宏观内$this将引用查询构建器实例

请注意，我将它们合二为一，因为您可能有多个from调用相同的查询，试图弄清楚什么去了哪里会很混乱