Witaj Gościu! ( Zaloguj | Rejestruj )

Forum PHP.pl

> [SF2][SF][Doctrine2] Zaawansowana wyszukiwarka i problem w zapytaniem (relacja wiele do jednego)
swiezak
post
Post #1





Grupa: Zarejestrowani
Postów: 159
Pomógł: 0
Dołączył: 21.08.2011

Ostrzeżenie: (0%)
-----

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 
  1. class ProductsController extends Controller
  2. {
  3.     /**
  4.      * Lists all Products entities.
  5.      */
  6.     public function indexAction()
  7.     {
  8.         $em = $this->getDoctrine()->getManager();
  9.         $entitiesLength = $em->getRepository('MlBackendBundle:Products')->counter();
  10.         $entities = $em->getRepository('MlBackendBundle:Products')->findAll();
  11.         $brands = $em->getRepository('MlBackendBundle:Products')->getBrands();
  12.         $availability = $em->getRepository('MlBackendBundle:Products')->getAvailability();
  13.  
  14.         return $this->render('MlBackendBundle:Products:index.html.twig', array(
  15.             'entitiesLength' => $entitiesLength,
  16.             'entities' => $entities,
  17.             'brands' => $brands,
  18.             'availability' => $availability,
  19.         ));
  20.     }
[PHP] pobierz, plaintext



W repozytorium zas:
[PHP] pobierz, plaintext 
  1. public function search($searchParam) {
  2.         extract($searchParam);
  3.         $qb = $this->createQueryBuilder('product');
  4.  
  5.         if(!empty($keyword))
  6.             $qb->andWhere('product.sku like :keyword or product.name like :keyword')
  7.                ->setParameter('keyword', '%'.$keyword.'%');
  8.         if(!empty($ids))
  9.             $qb->andWhere('product.id in (:ids)')->setParameter('ids', $ids);
  10.         if(!empty($sku))
  11.             $qb->andWhere('product.sku = :sku')->setParameter('sku', $sku);
  12.         if(!empty($name))
  13.             $qb->andWhere('product.name = :name')->setParameter('name', $name);
  14.  
  15.  
  16.         // ponizszy fragment nie zwraca poprawnych wynikow
  17.         if(!empty($brandId))
  18.             $qb->andWhere('product.brandId = :brandId')->setParameter('brandId', $brandId);
  19.  
  20.  
  21.         if(!empty($priceBrutto))
  22.             $qb->andWhere('product.priceBrutto >= :priceBrutto')->setParameter('priceBrutto', $priceBrutto);
  23.         if(!empty($isNew))
  24.             $qb->andWhere('product.isNew = :isNew')->setParameter('isNew', $isNew);
  25.         if(!empty($isPromo))
  26.             $qb->andWhere('product.isPromo = :isPromo')->setParameter('isPromo', $isPromo);
  27.         if(!empty($isPopular))
  28.             $qb->andWhere('product.isPopular = :isPopular')->setParameter('isPopular', $isPopular);
  29.         if(!empty($isActive))
  30.             $qb->andWhere('product.isActive = :isActive')->setParameter('isActive', $isActive);
  31.         if(!empty($sortBy)){
  32.             $sortBy = in_array($sortBy, array('sku', 'name', 'brands.id', 'priceNetto', 'priceBrutto', 'isActive', 'created', 'updated')) ? $sortBy : 'id';
  33.             $sortDir = ($sortDir == 'DESC') ? 'DESC' : 'ASC';
  34.             $qb->orderBy('product.' . $sortBy, $sortDir);
  35.         }
  36.         if(!empty($perPage)) $qb->setFirstResult(($page - 1) * $perPage)->setMaxResults($perPage);
  37.  
  38.        return new Paginator($qb->getQuery());
  39.     }
  40.  
  41.     private function fetch($query) {
  42.         $stmt = $this->getEntityManager()->getConnection()->prepare($query);
  43.         $stmt->execute();
  44.  
  45.         return $stmt->fetchAll();
  46.     }
  47.  
  48.     public function counter() {
  49.         $qb = $this->createQueryBuilder('product')->select('COUNT(product)');
  50.         return $qb->getQuery()->getSingleScalarResult();
  51.     }
  52.  
  53.     public function getBrands() {
  54.         return $this->fetch("select distinct id, name from brands");
  55.     }
  56.  
  57.     public function getAvailability() {
  58.         return $this->fetch("select distinct name as label from availability");
  59.     }
[PHP] pobierz, plaintext


Fragment pliku index.hmtl.twig:
[HTML] pobierz, plaintext 
  1.           <div class="form-group">
  2.             <label for="" class="col-sm-5 control-label">Producent</label>
  3.             <div class="col-sm-6">
  4.               <select id="activeselect" name="searchParam[brandId]" class="select2 no-search">
  5.                 <option value="">Wybierz opcję</option>
  6.                 {% for item in brands %}
  7.                   <option value="{{ item.id }}">{{ item.name }}</option>
  8.                 {% endfor %}
  9.               </select>
  10.             </div>
  11.           </div>
[HTML] pobierz, plaintext


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


W profilerze mam takie zapytanie:
[SQL] pobierz, plaintext 
  1. SELECT 
  2.   t0.id AS id_1, 
  3.   t0.sku AS sku_2, 
  4.   t0.name AS name_3, 
  5.   t0.slug AS slug_4, 
  6.   t0.vat_value AS vat_value_5, 
  7.   t0.price_netto AS price_netto_6, 
  8.   t0.price_brutto AS price_brutto_7, 
  9.   t0.old_price_netto AS old_price_netto_8, 
  10.   t0.old_price_brutto AS old_price_brutto_9, 
  11.   t0.is_active AS is_active_10, 
  12.   t0.is_new AS is_new_11, 
  13.   t0.is_promo AS is_promo_12, 
  14.   t0.is_popular AS is_popular_13, 
  15.   t0.created AS created_14, 
  16.   t0.updated AS updated_15, 
  17.   t0.views AS views_16, 
  18.   t0.short_description AS short_description_17, 
  19.   t0.description AS description_18, 
  20.   t0.hide_price AS hide_price_19, 
  21.   t0.meta_title AS meta_title_20, 
  22.   t0.meta_keywords AS meta_keywords_21, 
  23.   t0.meta_description AS meta_description_22, 
  24.   t0.availability_id AS availability_id_23, 
  25.   t0.brand_id AS brand_id_24 
  26. FROM 
  27.   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 
  1.     public function search($searchParam) {
  2.         extract($searchParam);
  3.         $qb = $this->createQueryBuilder('product')
  4.                 ->leftJoin('product.brands', 'b')
  5.                 ->addSelect('b');
  6.                 ->leftJoin('product.availability', 'a')
  7.                 ->addSelect('a');
  8.  
  9.         if(!empty($keyword))
  10.             $qb->andWhere('product.sku like :keyword or product.name like :keyword')
  11.                ->setParameter('keyword', '%'.$keyword.'%');
  12.         if(!empty($ids))
  13.             $qb->andWhere('product.id in (:ids)')->setParameter('ids', $ids);
  14.         if(!empty($sku))
  15.             $qb->andWhere('product.sku = :sku')->setParameter('sku', $sku);
  16.         if(!empty($name))
  17.             $qb->andWhere('product.name = :name')->setParameter('name', $name);
  18.  
  19.         if(!empty($brands))
  20.             $qb->andWhere('b.id = :brands')->setParameter('brands', $brands);
  21. }
[PHP] pobierz, plaintext


Kontroler:
[PHP] pobierz, plaintext 
  1.     public function indexAction()
  2.     {
  3.         $em = $this->getDoctrine()->getManager();
  4.         $entitiesLength = $em->getRepository('MlBackendBundle:Products')->counter();
  5.         $entities = $em->getRepository('MlBackendBundle:Products')->findAll();
  6.         $brands = $em->getRepository('MlBackendBundle:Brands')->findAll();
  7.         $categories = $em->getRepository('MlBackendBundle:Products')->getCategories();
  8.         $availability = $em->getRepository('MlBackendBundle:Availability')->findAll();
  9.  
  10.         return $this->render('MlBackendBundle:Products:index.html.twig', array(
  11.             'entitiesLength' => $entitiesLength,
  12.             'entities' => $entities,
  13.             'brands' => $brands,
  14.             'categories' => $categories,
  15.             'availability' => $availability,
  16.         ));
  17.     }
[PHP] pobierz, plaintext


index.html.twig
[HTML] pobierz, plaintext 
  1.           <div class="form-group">
  2.             <label for="" class="col-sm-5 control-label">Producent</label>
  3.             <div class="col-sm-6">
  4.               <select id="activeselect" name="searchParam[brands]" class="select2 no-search">
  5.                 <option value="">Wybierz opcję</option>
  6.                 {% for brand in brands %}
  7.                   <option value="{{ brand.id }}">{{ brand.name }}</option>
  8.                 {% endfor %}
  9.               </select>
  10.             </div>
  11.           </div>
[HTML] pobierz, plaintext
Go to the top of the page
+Quote Post

Posty w temacie
- swiezak   [SF2][SF][Doctrine2] Zaawansowana wyszukiwarka i problem w zapytaniem (relacja wiele do jednego)   6.02.2016, 15:45:44
- - lukaskolista   Polecam https://github.com/lexik/LexikFormFilterBu...   19.02.2016, 09:59:43

« Następny starszy · Frameworki · Następny nowszy »
 

Reply to this topicStart new topic
1 Użytkowników czyta ten temat (1 Gości i 0 Anonimowych użytkowników)
0 Zarejestrowanych:

 

Tryb wyświetlania: Przełącz na: Standardowy · Przełącz na: Linearny+ · Drzewo

Śledź ten temat · Wyślij temat na e-mail · Wydrukuj ten temat · Subskrybuj to forum

RSS Aktualny czas: 21.08.2025 - 05:24
Powered By IP.Board © 2025  IPS, Inc.
All changes by PHP.pl Administrators
Hosting zapewnia NQ.pl hosting, trac, svn