Doctrine: Manytomany’s Relationship with Extra Fields

Asked

Viewed 2,069 times

5

I have a many to many relationship between two tables: Products and Orders.

The Doctrine then generates a third table. So far quiet.

But wanted to generate in this third table more fields (Qtde and unit value at the time of purchase).

The question is: How do I make this relationship work in Doctrine (with direct Annotations in Entities) and how do I pick up/set after the amount and value of a particular order product (third table)?

The closest I could come to an explanation was this: https://stackoverflow.com/questions/15616157/doctrine-2-and-many-to-many-link-table-with-an-extra-field/15630665#15630665

But there is no explanation of how Arraycollection works for such cases nor how to pick or set the extra fields of the third table.

From now on I appreciate all the help you can give.

Table Products (Table 1)

Id           (pk)
Name
Value

Orders Table (Table 2)

Id           (pk)
TotalValue

Table Productsorders (Table of Institution)

Product      (fk)
Order        (fk)
Quantity
UnityValue

--

Entities

Below are the entities made according to instructions. Note, however, that the correct method of working with the extra columns is missing - i. e., how to do for and Entitymanager take the extra columns from the entity Productorder (through, of course, the link fields in the entities Product and Order).

It is also important to know how to proceed to make the relationship of this third table with another, since it does not have its own ID, since the ID is formed by the set of Product ID + Order ID.

<?php

/**
 * @Entity(repositoryClass="Repositories\ProductRepository")
 * @Table(name="Products")
 */
class Product
{
    /**
     * @Id
     * @Column(type="integer")
     * @GeneratedValue
     */
    protected $id;

    /**
     * @Column(length=100)
     */
    protected $name;

    /**
     * @Column(type="decimal", precision=10, scale=2)
     */
    protected $value;

    /**
     * @OneToMany(targetEntity="ProductOrder", mappedBy="product")
     */
    protected $orders;

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

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

    public function setId(int $id): Product
    {
        $this->id = $id;
        return $this;
    }

    public function getName(): string
    {
        return $this->name;
    }

    public function setName(string $name): Product
    {
        $this->name = $name;
        return $this;
    }

    public function getValue(): float
    {
        return $this->value;
    }

    public function setValue(float $value): Product
    {
        $this->value = $value;
        return $this;
    }

    public function getOrders()
    {
        return $this->orders;
    }

    public function addOrder(Order $order)
    {
        if ($this->getOrders()->contains($order)) {
            return $this;
        }

        $this->getOrders()->add($order);
        $order->addProduct($this);

        return $this;
    }

    public function removeOrder(Order $order)
    {
        if (!$this->getOrders()->contains($order)) {
            return $this;
        }

        $this->getOrders()->removeElement($order);
        $order->removeProduct($this);

        return $this;
    }
}

/**
 * @Entity(repositoryClass="Repositories\OrderRepository")
 * @Table(name="Orders")
 */
class Order
{
    /**
     * @Id
     * @Column(type="integer")
     * @GeneratedValue
     */
    protected $id;

    /**
     * @Column(type="decimal", precision=10, scale=2)
     */
    protected $totalValue;

    /**
     * @OneToMany(targetEntity="ProductOrder", mappedBy="order")
     */
    protected $products;

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

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

    public function setId(int $id): Order
    {
        $this->id = $id;
        return $this;
    }

    public function getTotalValue(): float
    {
        return $this->totalValue;
    }

    public function setTotalValue(float $totalValue): Order
    {
        $this->totalValue = $totalValue;
        return $this;
    }

    public function getProducts()
    {
        return $this->products;
    }

    public function addProduct(Product $product)
    {
        if ($this->getProducts()->contains($product)) {
            return $this;
        }

        $this->getProducts()->add($product);
        $product->addOrder($this);

        return $this;
    }

    public function removeProduct(Product $product)
    {
        if (!$this->getProducts()->contains($product)) {
            return $this;
        }

        $this->getProducts()->removeElement($product);
        $product->removeOrder($this);

        return $this;
    }
}

/**
 * @Entity(repositoryClass="Repositories\ProductOrderRepository")
 * @Table(name="ProductsOrders")
 */
class ProductOrder
{
    /**
     * @Id
     * @ManyToOne(targetEntity="Order", inversedBy="products")
     * @JoinColumn(name="order", referencedColumnName="id")
     */
    protected $order;

    /**
     * @Id
     * @ManyToOne(targetEntity="Product", inversedBy="orders")
     * @JoinColumn(name="product", referencedColumnName="id")
     */
    protected $product;

    /**
     * @Column(type="integer")
     */
    protected $quantity;

    /**
     * @Column(type="decimal", precision=10, scale=2)
     */
    protected $unityValue;

    public function getOrder()
    {
        return $this->order;
    }

    public function setOrder($order)
    {
        $this->order = $order;
        return $this;
    }

    public function getProduct()
    {
        return $this->product;
    }

    public function setProduct($product)
    {
        $this->product = $product;
        return $this;
    }

    public function getQuantity()
    {
        return $this->quantity;
    }

    public function setQuantity($quantity)
    {
        $this->quantity = $quantity;
        return $this;
    }

    public function getUnityValue()
    {
        return $this->unityValue;
    }

    public function setUnityValue($unityValue)
    {
        $this->unityValue = $unityValue;
        return $this;
    }
}

Example of the use of MS

<?php

$product = $em->getReference(User::class, 1);
$order = $em->getReference(User::class, 1);

$productOrder = new ProductOrder();
$productOrder
    ->setOrder($order)
    ->setProduct($product)
    ->setQuantity(5)
    ->setUnityValue(99.9);

// Isso está cadastrando corretamente.
$em->getRepository(ProductOrder::class)->create($productOrder);

// Porém, isso não está funcionando:
echo $order->getProducts()[0]->getQuantity();
php mysql doctrine relationship doctrine-2

  • Could you post your models? What the linked response suggests is that a third entity, something like a OrderItems

  • The third table is not orderitens. The third table (or how it should look) eh a Productsorders. Since I put the entities as they are at the moment.

  • To ease my need, I added as I am doing at the moment. Note that the registration works (although I believe it can be done differently) but I did not find a way to, by the entity Order take the extra data of the products of the order (as quantity purchased, for example)

2 answers

3


One way to do it is to treat the third table (Porductsorders) as a normal entity.

In this case you will not use Arraycollections (at least not in entities) and do the searches using only the intersection table.

To do so remove the option Inversedby of the entity’s annotations Productorder and remove Store#products and Product#Stores properties.

/**
 * @Entity(repositoryClass="Repositories\ProductOrderRepository")
 * @Table(name="ProductsOrders")
 */
class ProductOrder
{
    /**
     * @Id
     * @ManyToOne(targetEntity="Order")
     * @JoinColumn(name="order", referencedColumnName="id")
     */
    protected $order;

    /**
     * @Id
     * @ManyToOne(targetEntity="Product")
     * @JoinColumn(name="product", referencedColumnName="id")
     */
    protected $product;

    /**
     * @Column(type="integer")
     */
    protected $quantity;

    /**
     * @Column(type="decimal", precision=10, scale=2)
     */
    protected $unityValue;

    public function getOrder()
    {
        return $this->order;
    }

    public function setOrder($order)
    {
        $this->order = $order;
        return $this;
    }

    public function getProduct()
    {
        return $this->product;
    }

    public function setProduct($product)
    {
        $this->product = $product;
        return $this;
    }

    public function getQuantity()
    {
        return $this->quantity;
    }

    public function setQuantity($quantity)
    {
        $this->quantity = $quantity;
        return $this;
    }

    public function getUnityValue()
    {
        return $this->unityValue;
    }

    public function setUnityValue($unityValue)
    {
        $this->unityValue = $unityValue;
        return $this;
    }
}

You can make use of the intersection entity like this:

<?php

$product = $em->getReference(User::class, 1);
$order = $em->getReference(User::class, 1);

$productOrder = new ProductOrder();
$productOrder
    ->setOrder($order)
    ->setProduct($product)
    ->setQuantity(5)
    ->setUnityValue(99.9);

// Isso está cadastrando corretamente.
$em->getRepository(ProductOrder::class)->create($productOrder);

To search, it can be done like this:

<?php 

// Pesquisando:
$productOrder = $em->findBy([
    'order' => 1,
    'product' => 1
]);

Obs: Optionally, in your repository Productorderrepository, you can create the method findByIds($orderId, $productId), the method findOrderProducts($orderId) and the method findProductOrders($productId)

  • Thank you very much!

-1

According to this post the solution is to consider the Join table as a separate entity and map the relationships as One-To-Many.

In the implementation you showed the mapping of Product.orders and Orders.product should already return a list of ProductOrders, so you have access to the extra columns.

You’ll need to add a constructor to ProductOrders who receives the Product and the Order:   

ProductOrder{ 
    ...
    public function __construct(Order $order, Product $product, $quantity= 0, $unitValue=0) 
    { 
        $this->product= $product; 
        $this->order= $order; 
        $this->quantity= $quantity; 
        $this->unitValue= $unitValue; 
    } 
}

You can also add getters and setters the same way you did in other entities.

  • I appreciate the layout, but the need is mainly to access the extra fields by Entitymanager. Note also that the best way to make use of Arraycollections is requested for this case in Product#Orders and Order#products entities

  • @Szag-Ot Being that ProductOrder is mapped as an entity you will use the EM(Entitymanager) in the same way you would to make a query Product or Order. Regarding the ArrayCollections it was not clear what the doubt is.

  • Product and Order have ID. In Productorder, ID is a set of both entities. How to make a $em->find()? One way would be to use $em->findBy() passing both ids, but that doesn’t seem right. Also, when using the Product or Order entity to take the counter-table collection, the extra fields are not brought in. (Example: $Orders->getProducts() returns the list of Product entities related to the order, but NO EXTRA FIELDS). Understand why I need a complete answer....

  • to ease my need, I added as I am doing at the moment. Note that the registration works (although I believe it can be done differently) but I did not find a way to, by the entity Order take the extra data of the products of the order (as quantity purchased, for example)

Browser other questions tagged php mysql doctrine relationship doctrine-2

You are not signed in. Login or sign up in order to post.