How to add an index definition on Many-To-Many JoinTable in Doctrine?

Question

I have two Entities and a Join table for Many-To-Many relation:

Entities/Product.php

namespace App\Entities;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @ORM\Table(name="product")
 * @ORM\Entity(repositoryClass="App\Repositories\ProductRepository")
 */
class Product
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     * @ORM\Column(type="integer")
     *
     * @var int
     */
    private $id;

    /**
     * Many Products have Many Processes
     *
     * @ORM\ManyToMany(targetEntity="Process", inversedBy="products")
     * @ORM\JoinTable(name="product_process")
     *
     * @var \Doctrine\Common\Collections\ArrayCollection
     */
    private $processes;

    public function __construct()
    {
        $this->processes = new ArrayCollection();
    }

}

Entities/Process.php

namespace App\Entities;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @ORM\Table(name="process")
 * @ORM\Entity(repositoryClass="App\Repositories\ProcessRepository")
 */
class Process
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     * @ORM\Column(type="integer")
     *
     * @var int
     */
    private $id;

    /**
     * Many Processes have Many Products
     *
     * @ORM\ManyToMany(targetEntity="Product", mappedBy="processes")
     *
     * @var \Doctrine\Common\Collections\ArrayCollection
     */
    private $products;

    public function __construct()
    {
        $this->products = new ArrayCollection();
    }

}

When running doctrine:migrations:diff command it created product, process and product_process tables.

I want to create indexes and foreign keys on the product_process join table.

Is this possible through Doctrine ORM annotations on one of the other two Entities (Product or Process)?

score 2 · Answer 1 · edited Aug 16 '19 at 10:41

2

Your example should generate a SQL like this one:

CREATE TABLE product (
    id INT AUTO_INCREMENT NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB;

CREATE TABLE product_process (
    product_id INT NOT NULL,
    process_id INT NOT NULL,
    PRIMARY KEY(product_id, process_id)
) ENGINE = InnoDB;

CREATE TABLE process (
    id INT AUTO_INCREMENT NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB;

ALTER TABLE product_process ADD FOREIGN KEY (product_id) REFERENCES Product(id);
ALTER TABLE product_process ADD FOREIGN KEY (process_id) REFERENCES Process(id);

The generated product_process table already has the necessary primary key indexes and foreign keys as declared in your association mapping.

This behavior is documented here.

If you need anything else, you'll need to create a separate entity ProductProcess, and declare two OneToMany associations on that entity (and if you want, inverse ManyToOne relationships on the associated entities).

Additional mapping information can be declared on that entity directly.

edited Aug 16 '19 at 10:41

fsasvari

1,891
19
27

answered Aug 16 '19 at 10:31

yivi

42,438
18
116
138

Yeah, it generated those SQL migrations, but the problem is that I added some indexes to it, something like: "CREATE TABLE product_process (product_id INT NOT NULL, process_id INT NOT NULL, INDEX IDX_product_process_product_id (product_id), INDEX IDX_product_process_process_id (process_id), PRIMARY KEY(product_id, process_id))" – fsasvari Aug 16 '19 at 10:34
And when I run "migrations:diff" command it generates something like this: ALTER TABLE product_process RENAME INDEX idx_product_process_product_id TO IDX_57C403264584665A – fsasvari Aug 16 '19 at 10:37
You do not need to add indexes for the primary keys. [Primary keys are always indexed](https://stackoverflow.com/questions/1071180/is-the-primary-key-automatically-indexed-in-mysql). – yivi Aug 16 '19 at 10:40
Hmm then there is some kind of bug in Doctrine. Because when I paste your simple SQL it generates following index key: "fk_product_process_process_id". I don't know why, that kind of index is nowhere in SQL migration provided. And when I run migrations:diff after that it generates following SQL: "ALTER TABLE product_process RENAME INDEX fk_product_process_process_id TO IDX_57C403267EC2F574" – fsasvari Aug 16 '19 at 10:43
1

There is no bug. You can't control the names of the automatically generated indexes with Doctrine, doctrine will manage those automatically. If you set the indexes manually, Doctrine will rename them to whatever names it wants to generate. – yivi Aug 16 '19 at 10:44
Yeah, strange, when you generate migrations from those entities it creates all foreign keys and index keys, and if you set the namings as you like, than it generates them again in "migrations:diff" command. – fsasvari Aug 16 '19 at 10:48

How to add an index definition on Many-To-Many JoinTable in Doctrine?

1 Answers1