Orm: Custom Index definition with postgresql

[Vincz]

Hi! I was wondering how it would be possible to create GIN / GIST indexes with doctrine and the postgresql adapter.
Should we create our own type, like this:
https://github.com/jsor/doctrine-postgis/blob/master/src/Schema/SpatialIndexSqlGenerator.php

Or is there a built-in way in doctrine. Can't find any documentation on this.

Just trying to use the indexes from here : https://www.postgresql.org/docs/9.1/static/textsearch-indexes.html

Thx !

[Ocramius]

Or is there a built-in way in doctrine. Can't find any documentation on this.

No, the ORM has no way to map these index types at the moment.

In my opinion, this kind of information should only be mapped in SQL migrations, as it starts becoming extremely DB-vendor-specific.

[lcobucci]

Closing as per @Ocramius' comment.

[Grafikart]

Sorry to reopen such an old issue but I get the same problem. I add my index in migrations as you suggested but when I try to generate other migrations doctrine try to delete the index.

Is there a way to tell doctrine to ignore some custom indexes (inserted without annotation but with migrations) ?

[Grafikart]

If someone find this issue I found a post about this problem. It may be a good solution : https://www.liip.ch/en/blog/doctrine-and-generated-columns

[alexsegura]

You can do it like this, using index flags & a custom platform class.

<?xml version="1.0" encoding="utf-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping https://www.doctrine-project.org/schemas/orm/doctrine-mapping.xsd">
  <entity name="App\Person" table="person">
    <indexes>
      <index columns="first_name" flags="fulltext"/>
      <index columns="last_name" flags="fulltext"/>
    </indexes>
    <field name="firstName" column="first_name" type="string" nullable="true" />
    <field name="lastName" column="last_name" type="string" nullable="true" />
  </entity>
</doctrine-mapping>

<?php

namespace App\Doctrine\DBAL\Platforms;

use Doctrine\DBAL\Platforms\PostgreSQL94Platform as BasePostgreSQL94Platform;
use Doctrine\DBAL\Schema\Index;
use Doctrine\DBAL\Schema\Table;

class PostgreSQL94Platform extends BasePostgreSQL94Platform
{
    public function getIndexFieldDeclarationListSQL($columnsOrIndex): string
    {
        if ($columnsOrIndex instanceof Index && $columnsOrIndex->hasFlag('fulltext')) {

            return implode(', ', array_map(
                fn($column) => sprintf('%s gist_trgm_ops', $column),
                $columnsOrIndex->getQuotedColumns($this)
            ));
        }

        return parent::getIndexFieldDeclarationListSQL($columnsOrIndex);
    }


    public function getCreateIndexSQL(Index $index, $table)
    {
        if (!$index->hasFlag('fulltext')) {
            return parent::getCreateIndexSQL($index, $table);
        }

        if ($table instanceof Table) {
            $table = $table->getQuotedName($this);
        }

        $table = sprintf('%s USING GIST', $table);

        return parent::getCreateIndexSQL($index, $table);
    }
}

If you are using Symfony, you can override the platform class using the platform_service configuration key.