Reflect MySQL polymorphic association in Doctrine

2019-04-02 18:51发布

站内问答 / 前端开发
369 2 5

I'm using Doctrine 2.2.0 together with Codeigniter. I'm new to Doctrine (or to ORM in general).

I'm setting up entity and proxy classes based on YAML files which works fine. I do have problems in reflecting a polymorphic association in my DB in my Doctrine classes. I'm looking for a concrete example on how to implement the following polymorphic association in Doctrine.

In my DB I have a table called products. Depending on the value of field object_type and object_id I want to relate either to a record in the table videos or the table cars (I simplified it here). I want to keep both product types in 2 separate table because one has nothing to do with the other and both tables relate to other tables.

I took a look at the Doctrine Inheritance documentation and other examples, but it doesn't seem to help me. If possible I want to avoid adding the columns description and price to the tables videos and cars.

Many thanks!

|Table: products                                      |
|-----------------------------------------------------|
| ID | description | price  | object_type | object_id |
|-----------------------------------------------------|
| 1  | A video     | 20.00  | video       | 12        |
| 2  | A car       | 159.00 | car         | 5         |

|Table: videos                               |
|--------------------------------------------|
| ID | filename     | artist_id | date       |
|--------------------------------------------|
| 12 | somename.mp4 | 189       | 2011-02-15 |

|Table: cars                   |
|------------------------------|
| ID | brand_id | model | year |
|------------------------------|
| 5  | 17       | astra | 2010 |

2条回答
闹够了就滚
2楼-- · 2019-04-02 18:53

You were looking in the right place, inheritance mapping is certainly what you need to achieve this. From what I can tell this is a perfect example of "class table inheritance".

Check out the manual for some example / explanation on how to implement this.

http://docs.doctrine-project.org/projects/doctrine-orm/en/2.0.x/reference/inheritance-mapping.html#class-table-inheritance

查看更多
0人赞 添加讨论(0) 举报
老娘就宠你
3楼-- · 2019-04-02 19:07

Found the solution myself. I've listed below the different steps I've done. This answer is very big, but I tried to be as complete as possible.

The most important things are in the Product YAML files inheritanceType: JOINED, discriminatorColumn:, discriminatorMap: and the Video and Car entity classes class Video extends Product, class Car extends Product.

1) DB Schema

CREATE TABLE IF NOT EXISTS `artists` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `brands` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `cars` (
  `id` int(11) NOT NULL,
  `model` varchar(255) NOT NULL,
  `release_date` date NOT NULL,
  `brand_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) NOT NULL,
  `price` double NOT NULL,
  `object_type` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `videos` (
  `id` int(11) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `release_date` date NOT NULL,
  `artist_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

2) Doctrine YAML files (from which you need to create the Entity and proxy classes)

Entities.Artist.dcm.yml

Entities\Artist:
    type: entity
    table: artists
    id:
        id:
            type: integer
            primary: true
            notnull: true
            generator:
                strategy: AUTO
    fields:
        name:
            type: string(255)
            notnull: true
    oneToMany:
        videos:
            targetEntity: Video
            mappedBy: artist
    options:
        charset: utf8
        type: InnoDB

Entities.Brand.dcm.yml

Entities\Brand:
    type: entity
    table: brands
    id:
        id:
            type: integer
            primary: true
            notnull: true
            generator:
                strategy: AUTO
    fields:
        name:
            type: string(255)
            notnull: true
    oneToMany:
        cars:
            targetEntity: Car
            mappedBy: brand
    options:
        charset: utf8
        type: InnoDB

Entities.Car.dcm.yml

Entities\Car:
    type: entity
    table: cars
    fields:
        model:
            type: string
            notnull: true
        release_date:
            type: date
            notnull: true
    oneToOne: #unidirectional    
        brand:
            targetEntity: Brand
            joinColumns:
                brand_id:
                    referencedColumnName: id
    options:
        charset: utf8
        type: InnoDB

Entities.Product.dcm.yml

Entities\Product:
    type: entity
    table: products
    id:
        id:
            type: string
            primary: true
            notnull: true
            generator:
                strategy: AUTO
    fields:
        description:
            type: string(255)
            notnull: true
        price:
            type: decimal
            notnull: true
    inheritanceType: JOINED
    discriminatorColumn:
        name: object_type
        type: string
        length: 255
    discriminatorMap:
        video: Video
        car: Car
    options:
        charset: utf8
        type: InnoDB

Entities.Video.dcm.yml

Entities\Video:
    type: entity
    table: videos
    fields:
        file_name:
            type: string
            notnull: true
        release_date:
            type: date
            notnull: true
    oneToOne: #unidirectional    
        artist:
            targetEntity: Artist
            joinColumns:
                artist_id:
                    referencedColumnName: id
    options:
        charset: utf8
        type: InnoDB

3) Create Entity and Proxy classes

I use CodeIgniter. I've used Joel Verhagen's excellent guide

!! It's important you extend Video and Car with Product - See below !!

This results in the following Entity classes

Artist.php

namespace Entities;

use Doctrine\ORM\Mapping as ORM;

/**
 * Entities\Artist
 *
 * @ORM\Table(name="artists")
 * @ORM\Entity
 */
class Artist
{
    /**
     * @var integer $id
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

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

    /**
     * @var \Doctrine\Common\Collections\ArrayCollection
     *
     * @ORM\OneToMany(targetEntity="Entities\Video", mappedBy="artist")
     */
    private $videos;

    public function __construct()
    {
        $this->videos = new \Doctrine\Common\Collections\ArrayCollection();
    }

    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     *
     * @param string $name
     * @return Artist
     */
    public function setName($name)
    {
        $this->name = $name;
        return $this;
    }

    /**
     * Get name
     *
     * @return string 
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Add videos
     *
     * @param Entities\Video $videos
     */
    public function addVideo(\Entities\Video $videos)
    {
        $this->videos[] = $videos;
    }

    /**
     * Get videos
     *
     * @return Doctrine\Common\Collections\Collection 
     */
    public function getVideos()
    {
        return $this->videos;
    }
}

Brand.php

namespace Entities;

use Doctrine\ORM\Mapping as ORM;

/**
 * Entities\Brand
 *
 * @ORM\Table(name="brands")
 * @ORM\Entity
 */
class Brand
{
    /**
     * @var integer $id
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

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

    /**
     * @var \Doctrine\Common\Collections\ArrayCollection
     *
     * @ORM\OneToMany(targetEntity="Entities\Car", mappedBy="brand")
     */
    private $cars;

    public function __construct()
    {
        $this->cars = new \Doctrine\Common\Collections\ArrayCollection();
    }

    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     *
     * @param string $name
     * @return Brand
     */
    public function setName($name)
    {
        $this->name = $name;
        return $this;
    }

    /**
     * Get name
     *
     * @return string 
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Add cars
     *
     * @param Entities\Car $cars
     */
    public function addCar(\Entities\Car $cars)
    {
        $this->cars[] = $cars;
    }

    /**
     * Get cars
     *
     * @return Doctrine\Common\Collections\Collection 
     */
    public function getCars()
    {
        return $this->cars;
    }
}

Car.php

namespace Entities;

use Doctrine\ORM\Mapping as ORM;

/**
 * Entities\Car
 *
 * @ORM\Table(name="cars")
 * @ORM\Entity
 */
class Car extends Product //Important that Car extends Product
{
    /**
     * @var string $model
     *
     * @ORM\Column(name="model", type="string")
     */
    private $model;

    /**
     * @var date $release_date
     *
     * @ORM\Column(name="release_date", type="date")
     */
    private $release_date;

    /**
     * @var Entities\Brand
     *
     * @ORM\OneToOne(targetEntity="Entities\Brand")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="brand_id", referencedColumnName="id", unique=true)
     * })
     */
    private $brand;


    /**
     * Set model
     *
     * @param string $model
     * @return Car
     */
    public function setModel($model)
    {
        $this->model = $model;
        return $this;
    }

    /**
     * Get model
     *
     * @return string 
     */
    public function getModel()
    {
        return $this->model;
    }

    /**
     * Set release_date
     *
     * @param date $releaseDate
     * @return Car
     */
    public function setReleaseDate($releaseDate)
    {
        $this->release_date = $releaseDate;
        return $this;
    }

    /**
     * Get release_date
     *
     * @return date 
     */
    public function getReleaseDate()
    {
        return $this->release_date;
    }

    /**
     * Set brand
     *
     * @param Entities\Brand $brand
     * @return Car
     */
    public function setBrand(\Entities\Brand $brand = null)
    {
        $this->brand = $brand;
        return $this;
    }

    /**
     * Get brand
     *
     * @return Entities\Brand 
     */
    public function getBrand()
    {
        return $this->brand;
    }
}

Product.php

namespace Entities;

use Doctrine\ORM\Mapping as ORM;

/**
 * Entities\Product
 *
 * @ORM\Table(name="products")
 * @ORM\InheritanceType("JOINED")
 * @ORM\DiscriminatorColumn(name="", type="", length=)
 * @ORM\DiscriminatorMap({"video" = "Entities\Video", "car" = "Entities\Car"})
 * @ORM\Entity
 */
class Product
{
    /**
     * @var string $id
     *
     * @ORM\Column(name="id", type="string")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string $description
     *
     * @ORM\Column(name="description", type="string", length=255)
     */
    private $description;

    /**
     * @var decimal $price
     *
     * @ORM\Column(name="price", type="decimal")
     */
    private $price;


    /**
     * Get id
     *
     * @return string 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set description
     *
     * @param string $description
     * @return Product
     */
    public function setDescription($description)
    {
        $this->description = $description;
        return $this;
    }

    /**
     * Get description
     *
     * @return string 
     */
    public function getDescription()
    {
        return $this->description;
    }

    /**
     * Set price
     *
     * @param decimal $price
     * @return Product
     */
    public function setPrice($price)
    {
        $this->price = $price;
        return $this;
    }

    /**
     * Get price
     *
     * @return decimal 
     */
    public function getPrice()
    {
        return $this->price;
    }
}

Video.php

namespace Entities;

use Doctrine\ORM\Mapping as ORM;

/**
 * Entities\Video
 *
 * @ORM\Table(name="videos")
 * @ORM\Entity
 */
class Video extends Product //Important that Video extends Product
{
    /**
     * @var string $file_name
     *
     * @ORM\Column(name="file_name", type="string")
     */
    private $file_name;

    /**
     * @var date $release_date
     *
     * @ORM\Column(name="release_date", type="date")
     */
    private $release_date;

    /**
     * @var Entities\Artist
     *
     * @ORM\OneToOne(targetEntity="Entities\Artist")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="artist_id", referencedColumnName="id", unique=true)
     * })
     */
    private $artist;


    /**
     * Set file_name
     *
     * @param string $fileName
     * @return Video
     */
    public function setFileName($fileName)
    {
        $this->file_name = $fileName;
        return $this;
    }

    /**
     * Get file_name
     *
     * @return string 
     */
    public function getFileName()
    {
        return $this->file_name;
    }

    /**
     * Set release_date
     *
     * @param date $releaseDate
     * @return Video
     */
    public function setReleaseDate($releaseDate)
    {
        $this->release_date = $releaseDate;
        return $this;
    }

    /**
     * Get release_date
     *
     * @return date 
     */
    public function getReleaseDate()
    {
        return $this->release_date;
    }

    /**
     * Set artist
     *
     * @param Entities\Artist $artist
     * @return Video
     */
    public function setArtist(\Entities\Artist $artist = null)
    {
        $this->artist = $artist;
        return $this;
    }

    /**
     * Get artist
     *
     * @return Entities\Artist 
     */
    public function getArtist()
    {
        return $this->artist;
    }
}

3) Creating a new Car and a new Video

This is what goes in my CodeIgniter controller. The code presumes you've created an artist with the name Metallica and a brand with the name Ford.

public function createVideo()
{
    //Instantiate new Entities\Video object
    $video = new Entities\Video;

    //Since it extends Entities\Product you can set the common Product properties
    $video->setDescription('This is a Metallica clip');
    $video->setPrice(19.95);

    //Setting the custom Video properties
    $artist = $this->doctrine->em->getRepository('Entities\Artist')->findOneBy(array('name' => 'Metallica'));
    $video->setArtist($artist);
    $video->setReleaseDate(new DateTime());
    $video->setFileName('metallica.mp4');

    //Save
    $this->doctrine->em->persist($video);
    $this->doctrine->em->flush();
}

public function createCar()
{
    //Instantiate new Entities\Car object
    $car = new Entities\Car;

    //Since it extends Entities\Product you can set the common Product properties
    $car->setDescription('This is Ford Mondeo of 2011');
    $car->setPrice(19.95);

    //Setting the custom Car properties
    $brand = $this->doctrine->em->getRepository('Entities\Brand')->findOneBy(array('name' => 'Ford'));
    $car->setBrand($brand);
    $car->setReleaseDate(DateTime::createFromFormat('Y-m-d', '2011-11-15'));
    $car->setModel('Mondeo');

    //Save
    $this->doctrine->em->persist($car);
    $this->doctrine->em->flush();
}

4) Extracting all products

An example on how to extract all Products

public function extractAllProducts()
{
    $products = $this->doctrine->em->getRepository('Entities\Product')->findAll();
    foreach($products as $product)
    {
        printf('%s for € %s<br />', $product->getDescription(), $product->getPrice());
    }
}

This results into

This is a Metallica clip for € 19.95
This is Ford Mondeo of 2011 for € 19.95

An example onn how to extract all Videos

public function extractAllVideos()
{
    $videos = $this->doctrine->em->getRepository('Entities\Video')->findAll();
    foreach($videos as $video)
    {
        printf('%s, released %s for € %s<br />', $video->getDescription(),  $video->getReleaseDate()->format('Y'), $video->getPrice());
    }
}

This results into

This is a Metallica clip, released 2012 for € 19.95
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)