I have a text field in my database and a index on it for the first 10 characters. How do I specify that in my Doctrine Entity?
I can't find any information about database specific options for indexes anywhere :/
This is my "partial" MySQL create statement:
KEY `sourceaddr_index` (`sourceaddr`(10)),
And this is my @Index in doctrine:
@ORM\Index(name="sourceaddr_index", columns={"sourceaddr"}, options={}),
This dosnt interfere with the regular use, but I noticed the problem when deploying development to a new laptop, and creating the database based on my entities...
Any help would be appreciated :)
Possible since Doctrine 2.9, see: https://github.com/doctrine/dbal/pull/2412
@Index(name="slug", columns={"slug"}, options={"lengths": {191}})
Unfortunately, Doctrine seem to be very picky with whitespace location, so e.g. update --dump-sql
yields:
DROP INDEX slug ON wp_terms;
CREATE INDEX slug ON wp_terms (slug(191));
and even if you execute those, they messages will stay there (tested with MariaDB 10.3.14).
I've had very good luck naming the index in Doctrine, after manually creating it in MySQL. It's not pretty or elegant, and it's prone to cause errors moving from dev to production if you forget to recreate the index. But, Doctrine seems to understand it respect it.
In my entity, I have the following definition. Doctrine ignores the length option - it's wishful thinking on my part.
/**
* Field
*
* @ORM\Table(name="field", indexes={
* @ORM\Index(name="field_value_bt", columns={"value"}, options={"length": 100})
* })
And in MySQL, I execute
CREATE INDEX field_value_bt ON field (value(100))
As far as I've seen, Doctrine just leaves the index alone so long as it's named the same.
In short: you can't set this within Doctrine. Doctrine's ORM is specifically focused on cross vendor compatability and the type of index you're describing, though supported in many modern RDBMS, is somewhat outside the scope of Doctrine to handle.
Unfortunately there isn't an easy way around this if you use Doctrine's schema updater (in Symfony that would be php app/console doctrine:schema:update --force
) as if you manually update the database, Doctrine will sometimes, regress that change to keep things in sync.
In instances where I've needed something like this I've just set up a fixture that sends the relevant ALTER TABLE
statement via SQL. If you're going to be distributing your code (i.e. it may run on other/older databases) you can wrap the statement with a platform check to make sure.
It's not ideal but once your app/software stabilises, issues like this shouldn't happen all that often.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With