Created
August 8, 2017 01:35
-
-
Save Razoxane/3bc74900b4eb5c983eb0927fa13b95f5 to your computer and use it in GitHub Desktop.
Laravel - Create Index If Not Exists / Drop Index If Exists
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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'); | |
} | |
}); | |
} | |
} |
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
listTableDetails
function 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()
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Nice! had to adjust
$doctrineTable->hasIndex('singlecolumnindexname')
to
$doctrineTable->hasColumn('singlecolumnindexname')
to get this to work for me