Change charset and engine for Doctrine2's ManyToMany relationship's intermediary table

Question

I'm building this ManyToMany relationship between my two entities in Symfony2 and wish to have the linking table to be of charset latin1 and engine MyISAM (they are UTF-8 and InnoDB by default).

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 properly set this information for my entity tables but how can I do 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, from the $ php app/console doctrine:schema:validate command :

 [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 set this ManyToMany relationship between my two entities and still be able to specify both engine and charset for the newly created linking table?

Thanks for the help!

Answer 1

Unfortunately you cannot control the engine, character set, collation and other properties like this of join-tables through doctrine. As you can see, the @JoinTable annotation doesn't have an options argument, like @Table has.

But you can always manage these things manually and Doctrine won't complain (when validating the schema). So either create the join-table yourself, or have Doctrine create it and adjust it according to you wishes.

Personal advise

IMHO you shouldn't rely on Doctrine to create or alter your schema.

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

But for production usage this SQL should always be manually revised. Doctrine simply doesn't always generate the correct SQL. Like the case you have, there are other edge-cases that it doesn't take into account.

Other tools

I suggest you take a look at some other tools to manage database migrations.

Doctrine Migrations is one. Note that this tool relies on the Doctrine ORM to generate SQL (see migrations:diff), so you'll run into the same issues. The difference is that you can have it create "versions", which you can then revise and alter, until you have what you want. That final "version" is the thing you apply to your database.

Another popular migration tool is Phinx. It's more dynamic and framework independent (so it does not need Doctrine).

Answer 2

A trick might be to use another entity 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 it is anyway better to use this kind of intermediate entity since at one point in your application you may need to store intermediate data, and this would be the only way to do this (and changing every piece of code related to the relation would be quite painful).