John Davidson

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.