How to define a complex PostgreSQL index in Doctrine XML mapping?

Data and Databases
1

Hey folks, I’m having trouble with Doctrine migrations. I’ve got this fancy PostgreSQL index:

CREATE INDEX CONCURRENTLY idx
ON tags (tag_id, (data->>'parent_tag_id'))
WHERE jsonb_exists(data, 'parent_tag_id');

I want to stop Doctrine from trying to drop it when I run doctrine:migrations:diff. I’m using XML mapping and tried this:

<index name="idx" columns="tag_id,data->>'parent_tag_id'">
  <options>
    <option name="where">jsonb_exists(data, 'parent_tag_id')</option>
  </options>
</index>

But it’s not working. I get an error saying there’s no column named “data->>‘parent_tag_id’” on the tags table.

Anyone know how to properly define this kind of index in Doctrine XML mapping? I’m stuck and can’t find a solution. Thanks!

2

hey, have u tried using doctrine’s @ORM\Index annotation? it might work better for complex indexes. something like:

@ORM\Index(name=“idx”, columns={“tag_id”, “data”}, options={“where”: “jsonb_exists(data, ‘parent_tag_id’)”})

just a thought. let me kno if it helps!

3

hmm, that’s a tricky one! have u considered using a custom migration instead? that way u can define the index exactly as u need it. maybe something like:

$this->addSql('CREATE INDEX CONCURRENTLY idx ON tags...');

just a thought. what do u think? have u tried anything else?

4

I encountered a similar issue when working with complex PostgreSQL indexes in Doctrine. The problem lies in Doctrine’s limited support for advanced PostgreSQL features. A workaround I found effective is to use a raw SQL statement within your entity mapping. Try this approach:

<entity name="YourEntity" table="tags">
    <!-- Other mappings -->
    <indexes>
        <index name="idx">
            <options>
                <option name="lengths">
                    <option>CREATE INDEX CONCURRENTLY idx ON tags (tag_id, (data->>'parent_tag_id')) WHERE jsonb_exists(data, 'parent_tag_id')</option>
                </option>
            </options>
        </index>
    </indexes>
</entity>

This method bypasses Doctrine’s index parsing and allows you to define the index exactly as needed. Remember to handle the index creation manually in your deployment process, as Doctrine won’t manage it automatically.