Create FULLTEXT index for localized column – Pimcore

FulltextIndexForTechnicalDescription.php

<?php

namespace AppBundle\EventListener\ClassDefinition;

use Exception;
use Pimcore\Db;
use Pimcore\Event\Model\DataObject\ClassDefinitionEvent;
use Pimcore\Logger;
use Pimcore\Tool;

class FulltextIndexForTechnicalDescription
{
    public function createIndexes(ClassDefinitionEvent $event): void
    {
        $class = $event->getClassDefinition();
        if ($class->getId() != 1) {
            return;
        }

        $db = Db::get();
        $languages = Tool::getValidLanguages();

        foreach ($languages as $language) {
            $tableName = 'object_localized_query_1_' . $language;

            $tableExists = $db->fetchOne("SHOW TABLES LIKE '$tableName'");
            if (! $tableExists) {
                continue;
            }

            $columnExists = $db->fetchOne("SHOW COLUMNS FROM `$tableName` LIKE 'technicalDescription'");
            if (! $columnExists) {
                continue;
            }

            $indexExists = $db->fetchOne("SHOW INDEX FROM `$tableName` WHERE Key_name = 'ft_technicalDescription'");
            if ($indexExists) {
                continue;
            }

            try {
                $db->query(
                    "ALTER TABLE `$tableName` ADD FULLTEXT INDEX `ft_technicalDescription` (`technicalDescription`)"
                );
            } catch (Exception $e) {
                Logger::error(
                    'Error during create a FULLTEXT index for the table ' . $tableName . ': ' . $e->getMessage()
                );
            }
        }
    }
}

services.yml

services:
    AppBundle\EventListener\ClassDefinition\FulltextIndexForTechnicalDescription:
        tags:
            - { name: kernel.event_listener, event: pimcore.class.postUpdate, method: createIndexes }