I am trying to add USE INDEX() to the query builder in Laravel. I tried to follow similar steps to link and was kind of successful but I cannot manage the last bit and I am not sure my ad-hoc code has created a huge backdoor.

The target: The target of my exercise is to add Index to the query builder like below:

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

Here an option useIndex specifies the index that I am going to use for this query.

What I have done yet: Created a class named 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()
            );
        }
    }

Created a service provider named CustomDatabaseServiceProvider in App/Providers. Here I just manipulated registerConnectionServices function. I further commented Illuminate\Database\DatabaseServiceProvider::class, and added App\Providers\CustomDatabaseServiceProvider::class, to app.php in config directory.

<?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;
        });
    }
}

and finally created a class named QueryBuilder in App/Override. this is the problematic class:

<?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);
        }
    }
}

The issues here are:

1. The output does not contain USE INDEX
2. Is it safe to use str_replace to manipulate query?

CodePudding user response：

The query builder is macroable so in your service provider you can probably do:

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)");
    }
);

Then you could use this:

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

within the macro $this would refer to the query builder instance

Note that I have them both in one because you can potentially have multiple from calls for the same query and it would be a mess trying to figure out what goes where