Skip to content

Instantly share code, notes, and snippets.

@khalwat

khalwat/m240515_172552_add_index_to_demo_data.php

Last active May 16, 2024 14:47
Show Gist options
  • Save khalwat/5e8238f0efafe28cddaf508ee3509f7a to your computer and use it in GitHub Desktop.
Save khalwat/5e8238f0efafe28cddaf508ee3509f7a to your computer and use it in GitHub Desktop.
Download ZIP
Craft CMS 5 content migration that will create a functional index for custom fields, which are now stored as a JSON column in element_sites.content. Also handles dropping the index if the migration is reverted.
Raw
m240515_172552_add_index_to_demo_data.php
<?php
/**
* Craft CMS 5 content migration that will create a functional index for custom fields, which
* are now stored as a JSON column in element_sites.content. Also handles dropping the index if
* the migration is reverted.
*
* @licence MIT
* @link https://nystudio107.com
* @copyright Copyright (c) nystudio107
*/
namespace craft\contentmigrations;
use Craft;
use craft\base\FieldInterface;
use craft\db\Migration;
use craft\db\Table;
use craft\helpers\Console;
use Throwable;
use yii\db\Exception;
/**
* m240515_172552_add_index_to_demo_data migration.
*/
class m240515_172552_add_index_to_demo_data extends Migration
{
// The field handles we want to add database indexes for
public const FIELD_HANDLES = [
'demoData'
];
/**
* @inheritdoc
*/
public function safeUp(): bool
{
$entries = Craft::$app->getEntries();
$entryTypes = $entries->getAllEntryTypes();
foreach ($entryTypes as $entryType) {
$customFields = $entryType->getCustomFields();
foreach ($customFields as $customField) {
if (in_array($customField->handle, self::FIELD_HANDLES, true)) {
$this->addIndexForField($customField);
}
}
}
return true;
}
/**
* @inheritdoc
*/
public function safeDown(): bool
{
$entries = Craft::$app->getEntries();
$entryTypes = $entries->getAllEntryTypes();
foreach ($entryTypes as $entryType) {
$customFields = $entryType->getCustomFields();
foreach ($customFields as $customField) {
if (in_array($customField->handle, self::FIELD_HANDLES, true)) {
$this->dropIndexForField($customField);
}
}
}
return true;
}
/**
* @param FieldInterface $field
* @throws Exception
*/
protected function addIndexForField(FieldInterface $field): void
{
$db = $this->getDb();
$tableName = Table::ELEMENTS_SITES;
$sql = $field->getValueSql();
$indexName = $this->getIndexNameForField($field);
$cmd = null;
// MySQL
if ($db->getIsMysql()) {
// If there is no CAST returned (as there wont be for text and other types), add it
if (!str_starts_with($sql, 'CAST')) {
$sql = "CAST($sql AS CHAR(255))";
}
$cmd = $db->createCommand("ALTER TABLE $tableName ADD INDEX $indexName (( $sql COLLATE utf8mb4_bin )) USING BTREE;");
}
// Postgres
if ($db->getIsPgsql()) {
$cmd = $db->createCommand("CREATE INDEX $indexName ON $tableName ( $sql );");
}
// Execute the command, swallowing any errors
if ($cmd) {
try {
Console::output(Console::ansiFormat("Executing the following SQL to add the index:", [Console::FG_GREEN]));
Console::output(Console::ansiFormat($cmd->getRawSql(), [Console::FG_YELLOW]));
$cmd->execute();
} catch (Throwable $e) {
// That's fine
} finally {
return;
}
}
// If we got to here, we don't support this database type
throw new Exception("Database type not supported");
}
/**
* @param FieldInterface $field
* @return void
*/
protected function dropIndexForField(FieldInterface $field): void
{
$indexName = $this->getIndexNameForField($field);
try {
$this->dropIndex($indexName, Table::ELEMENTS_SITES);
} catch (Throwable $e) {
// That's fine
}
}
/**
* @param FieldInterface $field
* @return string
*/
protected function getIndexNameForField(FieldInterface $field): string
{
return $field->handle . '_' . str_replace('-', '_', $field->layoutElement->uid);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment