John Davidson

php - Doctrine many-to-many relationship with value in the middle

0 comments
Message:


I have a many-to-many relationship like this:



Everything is pretty classic with the only difference that classified_attribute table has a value column, idea behind it is pretty straightforward - to have a flexible list where i can map any attribute to any ad and set a value to it, and using SQL i am getting results i want:


SELECT
`classified`.`name`,
`attribute`.`name`,
`classified_attribute`.`value`
FROM `classified`
LEFT JOIN classified_attribute
ON classified.id = classified_attribute.classified_id
LEFT JOIN attribute
ON classified_attribute.attribute_id = attribute.id

Results:



But now i don't understand how to reproduce these results using Doctrine


#[ORM\Entity(repositoryClass: ClassifiedRepository::class)]
#[ORM\Table(name: 'classified')]
class Classified
{
#[ORM\Id]
#[ORM\GeneratedValue]
#[ORM\Column]
private int $id;

#[ORM\Column(length: 100)]
private string $name;


#[ORM\ManyToMany(targetEntity: Attribute::class)]
#[ORM\JoinTable(name: 'classified_attribute')]
#[ORM\JoinColumn(name: 'attribute_id', referencedColumnName: 'id')]
#[ORM\InverseJoinColumn(name: 'classified_id', referencedColumnName: 'id')]
private Collection $attributes;

# getters setters
}

I do get a list of attributes, but of course it's without value column, since attribute entity is mapped to attribute table which doesn't have it.


I've found a solution for Java: https://www.baeldung.com/jpa-many-to-many#many-to-many-using-a-composite-key, but is sit possible in Doctrine?



My Answer:
In Doctrine, you can create a many-to-many relationship with an additional value in the middle table by using a ManyToMany association with an Entity that represents the intermediate table.

Here's an example of how you can set up your entities:

1. Classified Entity:
php
/**
* @Entity
*/
class Classified
{
// your other properties

/**
* @ManyToMany(targetEntity="Attribute")
* @JoinTable(name="classified_attribute",
* joinColumns={@JoinColumn(name="classified_id", referencedColumnName="id")},
* inverseJoinColumns={@JoinColumn(name="attribute_id", referencedColumnName="id")}
* )
*/
private $attributes;

// getter and setter for attributes
}


2. Attribute Entity:
php
/**
* @Entity
*/
class Attribute
{
// your other properties

// no need to define the ManyToMany relationship here
}


3. ClassifiedAttribute Entity:
php
/**
* @Entity
*/
class ClassifiedAttribute
{
/**
* @Id
* @ManyToOne(targetEntity="Classified")
*/
private $classified;

/**
* @Id
* @ManyToOne(targetEntity="Attribute")
*/
private $attribute;

/**
* @Column(type="string")
*/
private $value;

// getters and setters for classified, attribute, and value
}


With this setup, you can now query the Classified entity and access the attributes with their values through the ClassifiedAttribute entity.

Here's an example of how you can retrieve the results using Doctrine:

php
$classified = $entityManager->getRepository(Classified::class)->find($classifiedId);

foreach ($classified->getAttributes() as $classifiedAttribute) {
$attributeName = $classifiedAttribute->getAttribute()->getName();
$value = $classifiedAttribute->getValue();

// do something with $attributeName and $value
}


This way, you can reproduce the results you want using Doctrine with a many-to-many relationship with a value in the middle table.

Rate this post

3 of 5 based on 7130 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us