Mam tabele produktów i producentow polaczonych relacja wiele do jednego.

Probuje wykonac wyszukiwarke, ktora by wyszukiwala produkty danego producenta, jednak mam problem z napisaniem odpowiedniego zapytania.

W kontrolerze mam nastepujacy kod:

[PHP] pobierz, plaintext 
class ProductsController extends Controller
{
    /**
     * Lists all Products entities.
     */
    public function indexAction()
    {
        $em = $this->getDoctrine()->getManager();
        $entitiesLength = $em->getRepository('MlBackendBundle:Products')->counter();
        $entities = $em->getRepository('MlBackendBundle:Products')->findAll();
        $brands = $em->getRepository('MlBackendBundle:Products')->getBrands();
        $availability = $em->getRepository('MlBackendBundle:Products')->getAvailability();
 
        return $this->render('MlBackendBundle:Products:index.html.twig', array(
            'entitiesLength' => $entitiesLength,
            'entities' => $entities,
            'brands' => $brands,
            'availability' => $availability,
        ));
    }
[PHP] pobierz, plaintext 

W repozytorium zas:

[PHP] pobierz, plaintext 
public function search($searchParam) {
        extract($searchParam);
        $qb = $this->createQueryBuilder('product');
 
        if(!empty($keyword))
            $qb->andWhere('product.sku like :keyword or product.name like :keyword')
               ->setParameter('keyword', '%'.$keyword.'%');
        if(!empty($ids))
            $qb->andWhere('product.id in (:ids)')->setParameter('ids', $ids);
        if(!empty($sku))
            $qb->andWhere('product.sku = :sku')->setParameter('sku', $sku);
        if(!empty($name))
            $qb->andWhere('product.name = :name')->setParameter('name', $name);
 
 
        // ponizszy fragment nie zwraca poprawnych wynikow
        if(!empty($brandId))
            $qb->andWhere('product.brandId = :brandId')->setParameter('brandId', $brandId);
 
 
        if(!empty($priceBrutto))
            $qb->andWhere('product.priceBrutto >= :priceBrutto')->setParameter('priceBrutto', $priceBrutto);
        if(!empty($isNew))
            $qb->andWhere('product.isNew = :isNew')->setParameter('isNew', $isNew);
        if(!empty($isPromo))
            $qb->andWhere('product.isPromo = :isPromo')->setParameter('isPromo', $isPromo);
        if(!empty($isPopular))
            $qb->andWhere('product.isPopular = :isPopular')->setParameter('isPopular', $isPopular);
        if(!empty($isActive))
            $qb->andWhere('product.isActive = :isActive')->setParameter('isActive', $isActive);
        if(!empty($sortBy)){
            $sortBy = in_array($sortBy, array('sku', 'name', 'brands.id', 'priceNetto', 'priceBrutto', 'isActive', 'created', 'updated')) ? $sortBy : 'id';
            $sortDir = ($sortDir == 'DESC') ? 'DESC' : 'ASC';
            $qb->orderBy('product.' . $sortBy, $sortDir);
        }
        if(!empty($perPage)) $qb->setFirstResult(($page - 1) * $perPage)->setMaxResults($perPage);
 
       return new Paginator($qb->getQuery());
    }
 
    private function fetch($query) {
        $stmt = $this->getEntityManager()->getConnection()->prepare($query);
        $stmt->execute();
 
        return $stmt->fetchAll();
    }
 
    public function counter() {
        $qb = $this->createQueryBuilder('product')->select('COUNT(product)');
        return $qb->getQuery()->getSingleScalarResult();
    }
 
    public function getBrands() {
        return $this->fetch("select distinct id, name from brands");
    }
 
    public function getAvailability() {
        return $this->fetch("select distinct name as label from availability");
    }
[PHP] pobierz, plaintext 

Fragment pliku index.hmtl.twig:

[HTML] pobierz, plaintext 
          <div class="form-group">
            <label for="" class="col-sm-5 control-label">Producent</label>
            <div class="col-sm-6">
              <select id="activeselect" name="searchParam[brandId]" class="select2 no-search">
                <option value="">Wybierz opcję</option>
                {% for item in brands %}
                  <option value="{{ item.id }}">{{ item.name }}</option>
                {% endfor %}
              </select>
            </div>
          </div>
[HTML] pobierz, plaintext 

W kodzie HTML widnieje zapis:

[HTML] pobierz, plaintext 
          <div class="form-group">
            <label for="" class="col-sm-5 control-label">Producent</label>
            <div class="col-sm-6">
              <select id="activeselect" name="searchParam[brandId]" class="select2 no-search">
                <option value="">Wybierz opcję</option>
                                  <option value="1">Novellini</option>
                                  <option value="2">Deante</option>
                                  <option value="3">Duravit</option>
                                  <option value="4">Geberit</option>
                                  <option value="5">Grohe</option>
                                  <option value="6">Hansgrohe</option>
                                  <option value="7">Kludi</option>
                                  <option value="8">Koło</option>
                                  <option value="9">Ravak</option>
                                  <option value="10">Roca</option>
                              </select>
            </div>
          </div>
[HTML] pobierz, plaintext 

W profilerze mam takie zapytanie:

[SQL] pobierz, plaintext 
SELECT 
  t0.id AS id_1, 
  t0.sku AS sku_2, 
  t0.name AS name_3, 
  t0.slug AS slug_4, 
  t0.vat_value AS vat_value_5, 
  t0.price_netto AS price_netto_6, 
  t0.price_brutto AS price_brutto_7, 
  t0.old_price_netto AS old_price_netto_8, 
  t0.old_price_brutto AS old_price_brutto_9, 
  t0.is_active AS is_active_10, 
  t0.is_new AS is_new_11, 
  t0.is_promo AS is_promo_12, 
  t0.is_popular AS is_popular_13, 
  t0.created AS created_14, 
  t0.updated AS updated_15, 
  t0.views AS views_16, 
  t0.short_description AS short_description_17, 
  t0.description AS description_18, 
  t0.hide_price AS hide_price_19, 
  t0.meta_title AS meta_title_20, 
  t0.meta_keywords AS meta_keywords_21, 
  t0.meta_description AS meta_description_22, 
  t0.availability_id AS availability_id_23, 
  t0.brand_id AS brand_id_24 
FROM 
  products t0
[SQL] pobierz, plaintext 

Gdzie robie blad? Prosze o jakas wskazowke.

Poprawilem kod i teraz juz dziala.

Dla zainteresowanych tematem:

Repozytorium

[PHP] pobierz, plaintext 
    public function search($searchParam) {
        extract($searchParam);
        $qb = $this->createQueryBuilder('product')
                ->leftJoin('product.brands', 'b')
                ->addSelect('b');
                ->leftJoin('product.availability', 'a')
                ->addSelect('a');
 
        if(!empty($keyword))
            $qb->andWhere('product.sku like :keyword or product.name like :keyword')
               ->setParameter('keyword', '%'.$keyword.'%');
        if(!empty($ids))
            $qb->andWhere('product.id in (:ids)')->setParameter('ids', $ids);
        if(!empty($sku))
            $qb->andWhere('product.sku = :sku')->setParameter('sku', $sku);
        if(!empty($name))
            $qb->andWhere('product.name = :name')->setParameter('name', $name);
 
        if(!empty($brands))
            $qb->andWhere('b.id = :brands')->setParameter('brands', $brands);
}
[PHP] pobierz, plaintext 

Kontroler:

[PHP] pobierz, plaintext 
    public function indexAction()
    {
        $em = $this->getDoctrine()->getManager();
        $entitiesLength = $em->getRepository('MlBackendBundle:Products')->counter();
        $entities = $em->getRepository('MlBackendBundle:Products')->findAll();
        $brands = $em->getRepository('MlBackendBundle:Brands')->findAll();
        $categories = $em->getRepository('MlBackendBundle:Products')->getCategories();
        $availability = $em->getRepository('MlBackendBundle:Availability')->findAll();
 
        return $this->render('MlBackendBundle:Products:index.html.twig', array(
            'entitiesLength' => $entitiesLength,
            'entities' => $entities,
            'brands' => $brands,
            'categories' => $categories,
            'availability' => $availability,
        ));
    }
[PHP] pobierz, plaintext 

index.html.twig

[HTML] pobierz, plaintext 
          <div class="form-group">
            <label for="" class="col-sm-5 control-label">Producent</label>
            <div class="col-sm-6">
              <select id="activeselect" name="searchParam[brands]" class="select2 no-search">
                <option value="">Wybierz opcję</option>
                {% for brand in brands %}
                  <option value="{{ brand.id }}">{{ brand.name }}</option>
                {% endfor %}
              </select>
            </div>
          </div>
[HTML] pobierz, plaintext 

Polecam https://github.com/lexik/LexikFormFilterBundle/