-
-
Save Razoxane/3bc74900b4eb5c983eb0927fa13b95f5 to your computer and use it in GitHub Desktop.
| <?php | |
| use Illuminate\Support\Facades\Schema; | |
| use Illuminate\Database\Schema\Blueprint; | |
| use Illuminate\Database\Migrations\Migration; | |
| class LaravelConditionalIndexMigration extends Migration | |
| { | |
| /** | |
| * Run the migrations. | |
| * | |
| * @return void | |
| */ | |
| public function up() | |
| { | |
| Schema::table('tablename', function (Blueprint $table) { | |
| $sm = Schema::getConnection()->getDoctrineSchemaManager(); | |
| $doctrineTable = $sm->listTableDetails('tablename'); | |
| if (! $doctrineTable->hasIndex('singlecolumnindexname')) { | |
| $table->index('column1', 'singlecolumnindexname'); | |
| } | |
| if (! $doctrineTable->hasIndex('multicolumnindexname')) { | |
| $table->index(['column2', 'column3'], 'multicolumnindexname'); | |
| } | |
| }); | |
| } | |
| /** | |
| * Reverse the migrations. | |
| * | |
| * @return void | |
| */ | |
| public function down() | |
| { | |
| Schema::table('tablename', function (Blueprint $table) { | |
| $sm = Schema::getConnection()->getDoctrineSchemaManager(); | |
| $doctrineTable = $sm->listTableDetails('tablename'); | |
| if ($doctrineTable->hasIndex('singlecolumnindexname')) { | |
| $table->dropIndex('singlecolumnindexname'); | |
| } | |
| if ($doctrineTable->hasIndex('multicolumnindexname')) { | |
| $table->dropIndex('multicolumnindexname'); | |
| } | |
| }); | |
| } | |
| } |
Thank you!
NICE !!! Thanks :-)
Great.
You can even make it a trait if you need to do that a lot
<?php
namespace Migrations;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
trait MigrationIndex
{
public function _dropIndexIfExist($tableName, $indexName)
{
Schema::table($tableName, function (Blueprint $table) use ($tableName, $indexName) {
$sm = Schema::getConnection()->getDoctrineSchemaManager();
$doctrineTable = $sm->listTableDetails($tableName);
if ($doctrineTable->hasIndex($indexName)) {
$table->dropIndex($indexName);
}
});
}
}Then in your migration files...
use Migrations\MigrationIndex;
class RemoveProductIndex extends Migration
{
use MigrationIndex;
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
$this->_dropIndexIfExist('products', 'category_id');
}
Awesome! Just what I've been searching for. Thank you very much
this needs to be included in official release
Nice! had to adjust
$doctrineTable->hasIndex('singlecolumnindexname')
to
$doctrineTable->hasColumn('singlecolumnindexname')
to get this to work for me
Great! thanks
Thanks so much for this!
I create a wrap function for that now
`
protected function createIndexName($prefix , $table , $type, array $columns)
{
$index = strtolower($prefix.$table.''.implode('', $columns).'_'.$type);
return str_replace(['-', '.'], '_', $index);
}
`
`
public function createIndex(Blueprint &$table , array|string $indexColumns , $indexName = null){
$sm = Schema::getConnection()->getDoctrineSchemaManager();
$doctrineTable = $sm->listTableDetails($table->getTable());
$indexColumns = (array) $indexColumns;
$indexName = $indexName ?: $this->createIndexName( "", $table->getTable(),'index', $indexColumns);
if (! $doctrineTable->hasIndex($indexName)) {
$table->index($indexColumns, $indexName);
}
}
`
this helped me a lot, thank you!
It appears that the listTableDetails function is deprecated. There wasn't an obvious replacement from the documentation
I solved the problem this way:
$sm = Schema::getConnection()->getDoctrineSchemaManager();
$index_list = $sm->listTableIndexes('tablename');
if(in_array('indexname', $index_list)) {
$table->dropIndex('indexname');
}
It appears that the
listTableDetailsfunction is deprecated. There wasn't an obvious replacement from the documentationI solved the problem this way:
$sm = Schema::getConnection()->getDoctrineSchemaManager(); $index_list = $sm->listTableIndexes('tablename'); if(in_array('indexname', $index_list)) { $table->dropIndex('indexname'); }
I don't know in which version it appeared, but now there is a replacement : introspectTable()
I'm glad I found this, because that's exactly what I was looking for! Thanks for the comments!
I made a portable/reuseable solution for it (app/Domain/Migration/MigrationUtil.php):
<?php
declare(strict_types=1);
namespace App\Domain\Migration;
use Doctrine\DBAL\Exception;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
class MigrationUtil
{
/**
* INFO: https://gist.github.com/Razoxane/3bc74900b4eb5c983eb0927fa13b95f5
*
* @throws Exception
*/
public static function hasIndex(string $tableName, string $indexName): bool
{
$doctrineSchemaManager = Schema::getConnection()->getDoctrineSchemaManager();
return $doctrineSchemaManager->introspectTable($tableName)->hasIndex($indexName);
}
}usage:
<?php
declare(strict_types=1);
use App\Domain\Migration\MigrationUtil;
use Illuminate\Database\Migrations\Migration;
return new class() extends Migration {
public function up(): void
{
Schema::table('users', static function ($table) {
if (MigrationUtil::hasIndex('users', 'unique_email')) {
$table->dropUnique('unique_email');
}
if (!MigrationUtil::hasIndex('users', 'users_email_unique')) {
$table->unique('email');
}
});
}
};
Thanks! Great stuff 🙇♂️ 💯
In Laravel 11 Symfony's DBAL has been replaced by Laravel's native classes so above solutions does not work anymore. Here's the working dropIndexIfNotExists for Laravel 11.
<?php
namespace App\Traits;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
trait MigrationIndex
{
public function dropIndexIfExist(string $tableName, string $indexName): void
{
$indexes = Schema::getIndexes($tableName);
foreach ($indexes as $index) {
if ($index['name'] === $indexName) {
Schema::table($tableName, static function (Blueprint $table) use ($indexName) {
$table->dropIndex($indexName);
});
}
}
}
}
Thanks for the code sample..