How can I generated this MySQL Schema using Doctrine

Question

CREATE TABLE `languages` (
  `id` int(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `status` INT(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `languages_content` (
  `language_id` int(4) UNSIGNED NOT NULL,
  `language` int(4) UNSIGNED NOT NULL,  
  `label` varchar(50) NOT NULL,
  PRIMARY KEY (`language_id`,`language`),
  KEY `language` (`language`),
  CONSTRAINT `languages_content_idfk_1` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `languages_content_idfk_2` FOREIGN KEY (`language`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Is posible to do that? I already try many-to-many relationship, but this remove the 'label' field from the 'languages_content' table.

/**
 * Languages
 *
 * @ORM\Table(name="languages")
 * @ORM\Entity
 */
class Languages
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer", nullable=false, options={"unsigned"=true})
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=50, nullable=false)
     */
    private $name;

    /**
     * @var int
     *
     * @ORM\Column(name="status", type="integer", nullable=false)
     */
    private $status = '0';

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="created_at", type="datetime", nullable=false)
     */
    private $createdAt;

    /**
     * @var \Doctrine\Common\Collections\Collection 
     *
     * @ORM\ManyToMany(targetEntity="Languages", inversedBy="language")
     * @ORM\JoinTable(name="languages_content",
     *   joinColumns={
     *     @ORM\JoinColumn(name="language_id", referencedColumnName="id")
     *   },
     *   inverseJoinColumns={
     *     @ORM\JoinColumn(name="language", referencedColumnName="id")
     *   }
     * )
     */
    private $language = array();

I also try with many-to-one relationship, but this option add an autoincremential field (id) that doesn't exist in languages_content table, as you can see the languages_content is used to relate the translations with the languages ​​in the languages ​​table.

#[ORM\Entity(repositoryClass: LanguagesRepository::class)]
class Languages
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 50, nullable: false)] 
    private ?string $name = null;

    #[ORM\Column(nullable: true)] 
    private ?int $status = 0; 

    #[ORM\Column(nullable: true)]
    private ?\DateTimeImmutable $createdAt = null;

    #[ORM\OneToMany(mappedBy: 'languages', targetEntity: LanguagesContent::class)]
    private Collection $language;

Thanks in advance for your help or sugestions.

Answer 1

Doctrine's ManyToMany does not support additional fields so you need to make the join table (i.e. languages_content) yourself

To get rid of the autoincremented ID simply remove the $id and its getter from the entity then add a #[ORM\Id] annotation to both ManyToOnes, if you make a migration this will create a composite primary key like in your SQL example.

Here is an example from the doctrine documentation https://www.doctrine-project.org/projects/doctrine-orm/en/2.13/tutorials/composite-primary-keys.html#use-case-3-join-table-with-metadata