php idiot php idiot - 19 days ago 7
MySQL Question

Symfony - Building query for table created by many to many relation

I have 2 entities connected with many to many relation into a 3th table, and I want to get every color for an id of product:

/**
* @var ArrayCollection
*
* @ORM\ManyToMany(targetEntity="AppBundle\Entity\Color", inversedBy="products")
* @ORM\JoinTable(name="products_colors",
* joinColumns={@ORM\JoinColumn(name="product_id", referencedColumnName="id")},
* inverseJoinColumns={@ORM\JoinColumn(name="color_id", referencedColumnName="id")}
* )
*/
private $colors;


And in my second entity:

/**
* @var ArrayCollection
*
* @ORM\ManyToMany(targetEntity="AppBundle\Entity\Product", mappedBy="colors")
*/
private $products;


This is my query: (I was trying to make some joins but cant make it work)

class ProductRepository extends \Doctrine\ORM\EntityRepository
{
public function getColors($id)
{
$query = $this->createQueryBuilder('p')
->join('AppBundle\Entity\Color', 'c')
->where('c.product_id = :id')
->setParameter('id', $id)
->getQuery()
->getResult();

return $query;
}
}


I got this error: [Semantical Error] line 0, col 85 near 'product_id =': Error: Class AppBundle\Entity\Color has no field or association named product_id which I understand, but can't think of a way to make this work.

Answer

Symfony expects you to reference the entity (not id) of an object when mapped by ManyToMany relationship. Try:

class ProductRepository extends \Doctrine\ORM\EntityRepository
{
    public function getColors(Product $product)
    {
        $query = $this->createQueryBuilder('p')
            ->join('AppBundle\Entity\Color', 'c')
            ->where('c.product = :product')
            ->setParameter('product', $product)
            ->getQuery()
            ->getResult();

        return $query;
    }
}

Of course you'll have to modify your call to this function accordingly.

You can also skip the query creation altogether with the getter function in your entity; symfony automagically will do the query for you.

// class Product

private $colors; // as you have it set up already

/**
 * @return ArrayCollection|Color[]
 */

public function getColors()
{
    return $this->colors;
}