Change the encoding and engine for Doctrine2 ManyToMany.

Question

I am creating this ManyToMany relationship between my two objects in Symfony2 and want the link table to have charset latin1 and engine MyISAM (by default they are UTF-8 and InnoDB).

Here they are:

Entity \ Commande

 <?php // ... /** * Commande * * @ORM\Table(name="commande", options={"collate"="latin1_general_ci", "charset"="latin1", "engine":"MyISAM"}) * @ORM\Entity() */ class Commande { // ... /** * @var ArrayCollection * * @ORM\ManyToMany(targetEntity="Paiement", inversedBy="commandes", cascade={"persist"}) * @ORM\JoinTable(name="paiement_commande", * joinColumns={@ORM\JoinColumn(name="commande_id", referencedColumnName="id")}, * inverseJoinColumns={@ORM\JoinColumn(name="paiement_id", referencedColumnName="id")} * ) */ private $paiements; 

Entity \ Paiement

 <?php // ... /** * Paiement * * @ORM\Table(name="paiement", options={"collate"="latin1_general_ci", "charset"="latin1", "engine":"MyISAM"}) * @ORM\Entity() */ class Paiement { // ... /** * @var ArrayCollection * * @ORM\ManyToMany(targetEntity="Commande", mappedBy="paiements", cascade={"persist"}) * @ORM\JoinTable(name="paiement_commande") */ private $commandes; 

As you can see, I know how to correctly set this information for entity tables, but how to do this for the paiement_commande table?

---

I tried:

 /* * @ORM\JoinTable(name="paiement_commande", options={"collate"="latin1_general_ci", "charset"="latin1", "engine":"MyISAM"}) */ private $commandes; 

But I got the command $ php app/console doctrine:schema:validate :

  [Doctrine\Common\Annotations\AnnotationException] [Creation Error] The annotation @ORM\JoinTable declared on property Entity\Paiement::$commandes does not have a property named "options". Available properties: name, schema, joinColumns, inverseJoinColumns 

How can I establish this ManyToMany relationship between my two objects and still be able to specify both engine and charset for the newly created link table?

Thanks for the help!

Answer 1

Unfortunately, you cannot control the engine, character set, collation, and other properties such as join-tables through the doctrine. As you can see, the @JoinTable annotation @JoinTable not have an options argument, for example @Table .

But you can always manage these things manually, and Doctrine will not complain (when checking the scheme). Therefore, either create a connection table yourself, or create its Doctrine and configure it in accordance with your wishes.

Personal advice

IMHO you should not rely on Doctrine to create or modify your schema.

Using the Doctrine console commands to create and modify the database is great for your development database, and is also useful for getting an initial indication of what SQL you need (see orm:schema-tool:update --dump-sql ).

But for use in production, this SQL should always be revised manually. Doctrine simply does not always generate the correct SQL. Like you, there are other boundary cases that he does not take into account.

Other tools

I suggest you take a look at some other tools for managing database migrations.

Doctrine migrations are one. Note that this tool relies on Doctrine ORM to generate SQL (see migrations:diff ), so you will run into the same problems. The difference is that you can create “versions” that you can revise and change until you get what you want. This final “version” is what you apply to your database.

Another popular migration tool is Phinx . He is more dynamic and independent of structure (therefore, he does not need the Teaching).

Answer 2

The trick may be to use another object as a join table.

Simply put:

 <?php // ... /** * PaiementCommande * * @ORM\Table(name="paiement_commande", options={"collate"="latin1_general_ci", "charset"="latin1", "engine":"MyISAM"}) * @ORM\Entity() */ class PaiementCommande { // ... /** * * @ORM\ManyToOne(targetEntity="Commande") */ private $commande; /** * * @ORM\ManyToOne(targetEntity="Paiement") */ private $paiement; 

I think that in any case it is better to use this kind of intermediate object, because at some point in your application you might need to store intermediate data, and that would be the only way to do this (and change every piece of code related to the relation would be quite painful).

Answer 3

My advice would be to let Doctrine and InnoDB do where they are good, handling referential integrity and relationships. Perhaps you want to use MyISAM for search, so it’s better to create a copy of the relevant data for it.

You can connect all the events that Doctrine triggers and make a copy of the entity data into another table. These events are really powerful and allow you to connect to almost every scenario: http://doctrine-orm.readthedocs.org/en/latest/reference/events.html

You can create a subscriber in Symfony that focuses on handling all the events for this object: http://symfony.com/doc/current/cookbook/doctrine/event_listeners_subscribers.html

Answer 4

Doctrine uses foreign_keys for any relationship mapping - they are only available in InnoDB . In addition to this, the doctrine will fail if you try to execute a transaction on a non-transactional engine (e.g. MyISAM), here . After all, there are options in Doctrine 2.2 or 2.3