Меню

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.


Asabix Wins Clutch Award for Top Developer in Ukraine 20.05.2021 Asabix Wins Clutch Award for Top Developer in Ukraine
Today, there’s about 200 million active websites and 1.8 billion web applications. These numbers continue to grow each day, adding more to the clutter. The only way to stand out from the crowd these days is through innovation and creativity—and that’s where we come in!

Features of implementation of SCRUM principles in our company and theirefficiency 23.02.2021 Features of implementation of SCRUM principles in our company and theirefficiency
Implementation of SCRUM project management methods in our company, the principles and artifacts used, the results of their application with a spectrum of efficiency in the use of labor and time resources.

Back to the list