Меню

Implementation of the structure of the product database from the sql-ex.ru service using Doctrine2

logo
Extend your project on a solid basis using the built-in Doctrine2 functionality, try adding your own classes and tables. Description of the product database
Implementation of the structure of the product database from the sql-ex.ru service using Doctrine2

Features of using Doctrine2 to implement inheritance

Some entities often have common properties that should be placed in the base entity (table). A good example of this is the product database from the sql-ex.ru service, which many people have encountered when learning SQL. Let's try to implement the structure of the proposed database using Symfony and Doctrine2.

This database is interesting in that it serves as a good example of inheritance. It is based on the "product" entity, which has the fields Manufacturer, Model, and Type. In the original database, the Model field was used as a foreign key, but in Doctrine, duplication of this field in tables will be unnecessary. In addition, it makes sense to create a separate Id field for some systematization and extensibility. For the above-mentioned, let's create an Entity Product.

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 * @ORM\InheritanceType("JOINED")
 * @ORM\DiscriminatorColumn(name="type", type="string")
 * @ORM\DiscriminatorMap({"product" = "Product", "pc" = "PC"})
 */
class Product
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

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

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

    public function getId(): ?int {
        return $this->id;
    }

    public function getMaker(): ?string {
        return $this->maker;
    }

    public function setMaker(string $maker): self {
        $this->maker = $maker;
        return $this;
    }

    public function getModel(): ?string {
        return $this->model;
    }

    public function setModel(string $model): self {
        $this->model = $model;
        return $this;
    }
}

In addition, let's create a child class PC

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 */

class PC extends Product
{
    /**
     * @ORM\Column(type="string", length=255)
     */
    private $code;

    /**
     * @ORM\Column(type="integer")
     */
    private $speed;

    /**
     * @ORM\Column(type="integer")
     */
    private $ram;

    /**
     * @ORM\Column(type="integer")
     */
    private $hd;

    /**
     * @ORM\Column(type="integer")
     */
    private $cd;

    /**
     * @ORM\Column(type="float", scale=10, precision=2)
     */
    private $price;

    public function getCode() {
        return $this->code;
    }

    public function getSpeed() {
        return $this->speed;
    }

    public function getRam() {
        return $this->ram;
    }

    public function getHd() {
        return $this->hd;
    }

    public function getCd() {
        return $this->cd;
    }

    public function getPrice() {
        return $this->price;
    }

    public function setCode($value) {
        $this->code = $value;
        return $this;
    }

    public function setSpeed($value) {
        $this->speed = $value;
        return $this;
    }

    public function setRam($value) {
        $this->ram = $value;
        return $this;
    }

    public function setHd($value) {
        $this->hd = $value;
        return $this;
    }

    public function setCd($value) {
        $this->cd = $value;
        return $this;
    }

    public function setPrice($value) {
        $this->price = $value;
        return $this;
    }
}

Now let's take a look at the last three annotations for the Product class.

@ORM\InheritanceType("JOINED")  - here we indicate the JOINED inheritance type, thereby indicating that each Entity should be stored in a separate table with its own columns, without overloading, for example, Laptop with Printer columns, etc.

@ORM\DiscriminatorColumn(name="type", type="string") - here we indicate that it is the type field that will be used to determine the desired table, and the values for the type column are taken from the subsequent annotation.

ORM\DiscriminatorMap({"product" = "Product", "pc" = "PC"}) - here you can see that for the class Product in the type property the value product will be stored, and for PC - pc. We will add the values for Laptop and Printer later.

After setting up a database connection, you can use Doctrine to create a migration with the following command php bin/console doctrine:migrations:diff and, if everything works out well;), then migrate, - php bin/console doctrine:migrations:migrate

In general, the basis for the subsequent expansion of the project has already been made. In addition, you can add annotations to getters and setters and the types of received/returned values.

To implement the remaining tables, just add the classes

Now let's take look at the last three annotations for the Product class.

@ORM\InheritanceType("JOINED")  - here we indicate the JOINED inheritance type, thereby indicating that each Entity should be stored in a separate table with its own columns, without overloading Laptop table with Printer's table columns, for example.

@ORM\DiscriminatorColumn(name="type", type="string") - here we indicate that it is the "type" field that will be used to determine the desired table, and the values for the "type" column are taken from the subsequent annotation.

ORM\DiscriminatorMap({"product" = "Product", "pc" = "PC"}) - here you can see that for the class Product in the "type" property the value "product" will be stored, and for the PC - "pc". We will add the values for the Laptop and Printer classes later.

After setting up a database connection, you can use Doctrine to create a migration with the following command php bin/console doctrine:migrations:diff and, if everything works out well;), then migrate, - php bin/console doctrine:migrations:migrate

In general, the basis for the subsequent expansion of the project has already been made. In addition, you can add annotations to getters and setters and the typehints of received/returned values.

To implement the remaining tables, just add the classes:

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 */

class Laptop extends Product
{
    /**
     * @ORM\Column(type="string", length=255)
     */
    private $code;

    /**
     * @ORM\Column(type="integer")
     */
    private $speed;

    /**
     * @ORM\Column(type="integer")
     */
    private $ram;

    /**
     * @ORM\Column(type="integer")
     */
    private $hd;

    /**
     * @ORM\Column(type="integer")
     */
    private $screen;

    /**
     * @ORM\Column(type="float", scale=10, precision=2)
     */
    private $price;

    public function getCode() {
        return $this->code;
    }

    public function getSpeed() {
        return $this->speed;
    }

    public function getRam() {
        return $this->ram;
    }

    public function getHd() {
        return $this->hd;
    }

    public function getScreen() {
        return $this->screen;
    }

    public function getPrice() {
        return $this->price;
    }

    public function setCode($value) {
        $this->code = $value;
        return $this;
    }

    public function setSpeed($value) {
        $this->speed = $value;
        return $this;
    }

    public function setRam($value) {
        $this->ram = $value;
        return $this;
    }

    public function setHd($value) {
        $this->hd = $value;
        return $this;
    }

    public function setScreen($value) {
        $this->screen = $value;
        return $this;
    }

    public function setPrice($value) {
        $this->price = $value;
        return $this;
    }
}

and

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 */

class Printer extends Product
{
    /**
     * @ORM\Column(type="boolean")
     */
    private $color;

    /**
     * @ORM\Column(type="string")
     */
    private $printerType;

    /**
     * @ORM\Column(type="float", scale=10, precision=2)
     */
    private $price;

    public function getColor() {
        return $this->color;
    }

    public function setColor($value) {
        $this->color = $value;
    return $this;
    }

    public function getPrinterType() {
        return $this->printerType;
    }

    public function setPrinterType($value) {
        $this->printerType = $value;
        return $this;
    }

    public function getPrice() {
        return $this->price;
    }

    public function setPrice($value) {
        $this->price = $value;
        return $this;
    }
}

It remains to change the line 

@ORM\DiscriminatorMap({"product" = "Product", "pc" = "PC"})

for 

@ORM\DiscriminatorMap({"product" = "Product", "pc" = "PC", "laptop" = "Laptop", "printer" = "Printer"})

After that, we migrate again and get four tables: product - the base, and three derivative ones, which are combined with the base by means of the Id foreign key.

If we make a query using QueryBuilder in one of the controller methods, we get objects of the desired class:

   /**
     * @Route("/printer/list")
     */
    public function printerListAction() {

        $em = $this->getDoctrine()->getManager();

        /** @var QueryBuilder $qb */
        $qb = $em
            ->getRepository(Printer::class)
            ->createQueryBuilder('p')
        ;

        $results = $qb
            ->setMaxResults(20)
            ->getQuery()
            ->getArrayResult()
        ;

        return new JsonResponse(['status' => 'success', 'data' => [$results]]);
    }

where all the necessary classes are already imported:

use App\Entity\Printer;
use Doctrine\ORM\QueryBuilder;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\Routing\Annotation\Route;

Now, extending your project on a solid basis using built-in Doctrine2 functionality will be faster and easier, and you can try adding your classes and tables.


Key differences between Laravel and Symfony 07.09.2019 Key differences between Laravel and Symfony
Which of two frameworks Laravel or Symfony is better to choose for the implementation of the project? For what specific needs this or that framework should be used? Description of the functionality of the frameworks

Connect EasyPost API 07.10.2019 Connect EasyPost API
API EasyPost integration tips

Back to the list