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
5 of 5 based on 1615 votesComments