Trying to create a query with Symfony that allows me to access object of foreign key

So I have 3 entities:

Supermarket:

| id | supermarket_name |

Category:

| id | category_name |

Product:

supermarket_id and category_id are both many to one with with the id's of supermarket and category respectively.

I would like it so that when I select a supermarket I get all of the categories listed underneath that supermarket. I have been attempting to get this data with a query from the product entity via the supermarkets id that has been passed into the query method in my ProductRepository.php file which works successfully if I have the following:

public function findAllCategoriesBySupermarket($supermarketId)
    {
        return $this->getEntityManager()
            ->createQuery(
                "SELECT p
                FROM AppBundle:Product p
                WHERE p.supermarketId = $supermarketId"
            )
            ->getResult();
    }

I can then display the products successfully in a loop in my view with:

{{ product.categoryId.categoryName }}

The Problem is though because I am query from the products I will end up with more than one of the same category because several products can be assigned to the same category, so I attempted to fix this by adding distinct to the category:

public function findAllCategoriesBySupermarket($supermarketId)
    {
        return $this->getEntityManager()
            ->createQuery(
                "SELECT DISTINCT p.categoryId
                FROM AppBundle:Product p
                WHERE p.supermarketId = $supermarketId"
            )
            ->getResult();
    }

This worked fine when I tested it in phpmyadmin but unfortunately symfony is giving the following error:

[Semantical Error] line 0, col 18 near 'categoryId
': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

Not sure what how I can go about fixing this as I have tried lot's of different things with no luck. Any advice on how I could go about this would be greatly appreciated!

Solution

The issue is that you can not select a distinct Entity association by property in doctrine. You can however select the distinct identity. Assuming you want the Entity and not just the ids...

To accomplish this you need to define the associations between the two Entities as bi-directional.

Since you have an association on Product.category but not with Category.products you would need to create it.

/**
 * @ORM\Entity
 */
class Category 
{
   //...

   /**
    * @ORM\OneToMany(targetEntity="AppBundle\Entity\Product", mappedBy="category")
    */
   private $products;


    public function __construct()
    {
         $this->products = new \Doctrine\Common\Collections\ArrayCollection;
    }

    //...

   /**
    * get Products
    * @return Product[]|ArrayCollection
    */
    public function getProducts()
    {
        return $this->products;
    }

     /**
      * add Product
      * @param Product $product
      * @return $this
      */
     public function addProduct(Product $product)
     {
        $this->products->add($product);

         return $this;
     }

    /**
     * remove Product
     * @param Product $product
     * @return $this
     */
    public function removeProduct(Product $product)
    {
        $this->products->removeElement($product);

        return $this;
    }
}

Then include the association inversedBy in your Product.category property

/**
 * @ORM\Entity
 */
class Product
{

    //...

    /**
     * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Category", inversedBy="products")
     * @ORM\JoinColumn(name="category", referencedColumnName="id")
     */
    private $category;

    //...
}

You also can't select an associated entity without the root association. e.g.

SELECT DISTINCT c
FROM AppBundle:Product p
JOIN p.category c

'SELECT DISTINCT': Error: Cannot select entity through identification variables without choosing at least one root entity alias.

So to retrieve the distinct categories associated by products with the specified supermarket, you would write your DQL like so.

public function findAllCategoriesBySupermarket($supermarketId)
{
    return $this->_em->createQuery(
            'SELECT c
            FROM AppBundle:Category c
            JOIN c.products p WITH p.supermarketId = :supermarket'
        )
        ->setParameter('supermarket', $supermarketId)
        ->getResult();
}

Though I would like to note, the DISTINCT flag is redundant in this context, since it is implicit to selecting only Category (c).

You could also utilize SELECT c, p instead, to obtain an array of categories with their associated products.

{% for category in categories %}
    {{ category.name }}:
    {% for product in category.products %}
        {{ product.name }}
    {% endfor %}
{% endfor %}

Alternatively to retain the unidirectional relationship, you would need to use a subquery. But you would lose the ability to retrieve the Category.products associations as demonstrated above.

SELECT c
FROM AppBundle:Category c
WHERE c.id IN (
    SELECT DISTINCT IDENTITY(p.categoryId)
    FROM AppBundle:Product p
    WHERE p.supermarketId = :supermarket
)