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

Question

我正在尝试将USE INDEX()添加到Laravel中的查询生成器中。我试图按照类似的步骤进行链接，并取得了一定的成功，但我无法完成最后一点，我不确定我的特别代码是否创建了一个巨大的后门。

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

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

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

我已经做了什么：在App/Override中创建了一个名为Connection的类

<?php

namespace AppOverride;
class Connection extends IlluminateDatabaseMySqlConnection {
//@Override
public function query() {
return new QueryBuilder(
$this,
$this->getQueryGrammar(),
$this->getPostProcessor()
);
}
}

在App/Providers中创建了一个名为CustomDatabaseServiceProvider的服务提供程序。这里我刚刚操作了registerConnectionServices函数。我进一步评论了IlluminateDatabaseDatabaseServiceProvider::class,，并在config目录中将AppProvidersCustomDatabaseServiceProvider::class,添加到app.php中。

<?php
namespace AppProviders;
use AppOverrideConnection;
use IlluminateDatabaseDatabaseManager;
use IlluminateDatabaseQueryGrammarsGrammar;
use IlluminateDatabaseSchema;
use IlluminateContractsQueueEntityResolver;
use IlluminateDatabaseConnectorsConnectionFactory;
use IlluminateDatabaseEloquentFactory as EloquentFactory;
use IlluminateDatabaseEloquentModel;
use IlluminateDatabaseEloquentQueueEntityResolver;
use IlluminateSupportServiceProvider;
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;
});
}
}

并最终在App/Override中创建了一个名为CCD_ 10的类。这是有问题的类：

<?php
namespace AppOverride;
use IlluminateSupportFacadesCache;
class QueryBuilder extends IlluminateDatabaseQueryBuilder
{
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操作查询是否安全

Answer 1

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

IlluminateDatabaseQueryBuilder::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');

在宏CCD_ 11中将引用查询生成器实例

请注意，我把它们放在一个位置，因为同一个查询可能有多个from调用，而试图弄清楚的位置会很混乱