Symfony retrieve Unique values from EAV model

Question

I'm trying to make product filters but I can't generate a correct query

А quick look at the base db visually

here are my entities:

AttributeType:

/**
 * @ORM\Id()
 * @ORM\GeneratedValue()
 * @ORM\Column(type="integer")
 */
private $id;

/**
 * @ORM\Column(type="string", length=100, nullable=true)
 */
private $name;

/**
 * @ORM\OneToMany(targetEntity=AttributeValue::class, mappedBy="attributeType")
 */
private $attributeValue;

public function __construct()
{
    $this->attributeValue = new ArrayCollection();
}

AttributeValue:

/**
 * @ORM\Id()
 * @ORM\GeneratedValue()
 * @ORM\Column(type="integer")
 */
private $id;

/**
 * @ORM\ManyToOne(targetEntity=Product::class, inversedBy="attributeValues")
 */
private $product;

/**
 * @ORM\Column(type="string", length=100)
 */
private $value;

/**
 * @ORM\ManyToOne(targetEntity=AttributeType::class, inversedBy="attributeValue")
 */
private $attributeType;

For example AttributeType(Color) has AttributeValue(Red, Blue, Green) & i retrieve hundred of red, blue, green AttributeValue for a single Color option

that query returns options with all value(not unique):

        return $this->createQueryBuilder('at')
        ->innerJoin('at.attributeValue', 'attribute_value')
        ->addSelect('attribute_value')
        ->getQuery()
        ->getResult();

I tried to modify the request like this:

        return $this->createQueryBuilder('at')
    ->innerJoin('at.attributeValue', 'attribute_value')
    ->addSelect('attribute_value.value')->distinct()
    ->getQuery()
    ->getResult();

(there were other attempts, but they were all not even close)

How do I get unique values for each option?

I will be grateful for any help

And thx for your time.

Answer 1

I get unique values for each option

    public function findOptionsWithUniqueValue()
{
    $result = $this->getEntityManager()->createQueryBuilder()
        ->addSelect('attribute_type.name, attribute_value.value')
        ->distinct()
        ->from(AttributeType::class,'attribute_type')
        ->from(AttributeValue::class, 'attribute_value')
        ->andWhere('attribute_type.id = attribute_value.attributeType')
        ->getQuery()
        ->getResult()
        ;

    $out = [];
    while( $a = array_shift($result)) {
        $out[$a['name']][] = $a['value'];
    }

    return $out;
}