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

so have 3 entities:

supermarket:

| id | supermarket_name |

category:

| id | category_name |

product:

| id | product_name | supermarket_id | category_id |

supermarket_id , category_id both many 1 with id's of supermarket , category respectively.

i when select supermarket of categories listed underneath supermarket. have been attempting data query product entity via supermarkets id has been passed query method in productrepository.php file works if have following:

public function findallcategoriesbysupermarket($supermarketid)     {         return $this->getentitymanager()             ->createquery(                 "select p                 appbundle:product p                 p.supermarketid = $supermarketid"             )             ->getresult();     } 

i can display products in loop in view with:

{{ product.categoryid.categoryname }} 

the problem though because query products end more 1 of same category because several products can assigned same category, attempted fix adding distinct category:

public function findallcategoriesbysupermarket($supermarketid)     {         return $this->getentitymanager()             ->createquery(                 "select distinct p.categoryid                 appbundle:product p                 p.supermarketid = $supermarketid"             )             ->getresult();     } 

this worked fine when tested in phpmyadmin unfortunately symfony giving following error:

[semantical error] line 0, col 18 near 'categoryid ': error: invalid pathexpression. must statefieldpathexpression. 

not sure how can go fixing have tried lot's of different things no luck. advice on how go appreciated!

the issue can not select distinct entity association property in doctrine. can select distinct identity. assuming want entity , not ids...

to accomplish need define associations between 2 entities bi-directional.

since have association on product.category not category.products need 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;     }      //...     /**     * 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 association inversedby in product.category property

/**  * @orm\entity  */ class product {      //...      /**      * @orm\manytoone(targetentity="appbundle\entity\category", inversedby="products")      * @orm\joincolumn(name="category", referencedcolumnname="id")      */     private $category;      //... } 

you can't select associated entity without root association. e.g.

select distinct c appbundle:product p join p.category c 

'select distinct': error: cannot select entity through identification variables without choosing @ least 1 root entity alias.

so retrieve distinct categories associated products specified supermarket, write dql so.

public function findallcategoriesbysupermarket($supermarketid) {     return $this->_em->createquery(             'select c             appbundle:category c             join c.products p p.supermarketid = :supermarket'         )         ->setparameter('supermarket', $supermarketid)         ->getresult(); } 

though note, distinct flag redundant in context, since implicit selecting category (c).

you utilize select c, p instead, obtain array of categories associated products.

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

---

alternatively retain unidirectional relationship, need use subquery. lose ability retrieve category.products associations demonstrated above.

select c appbundle:category c c.id in (     select distinct identity(p.categoryid)     appbundle:product p     p.supermarketid = :supermarket ) 

wiki