Меню

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.


Iiko API integration tips 24.12.2019 Iiko API integration tips
Iiko API is a JSON API. Each request should be signed by a special temporary request token. You can get a token using your Iiko account username and password, provided by Iiko service.

A web-project optimization - 3 ways to increase webpages page speed 01.10.2019 A web-project optimization - 3 ways to increase webpages page speed
How increase webpages page speed in a simple way? You have developed a website design, found all the relevant images and ordered an expensive landing page text, but your visitor number is minimal. If you are familiar with such situation, do not hurry to spent your money for a contextual advertisement or other services to increase the visitor number. First of all you need a website optimization.

Back to the list