distinct

distinct.php

<?php 

public function indexAction()
    {
         // Param
        $params = $this->_helper->param();
        $supplierId = Zend_Auth::getInstance()->getIdentity()->id;

        // Mapping
        $mapping = array(
            'id' => 'brand.id',
            'brand_title' => 'brand_title',
            'category_title' => 'category_title',
            'saleAvailable' => 'saleAvailable',
            'stockAvailable' => 'stockAvailable'
        );

        $query = Doctrine_Query::create()
                    ->select('DISTINCT p.brand_id, p.category_id')
                    ->addSelect('(SELECT scbStock.stockAvailable
                            FROM Model_Entity_SupplierCategoryBrand scbStock
                            WHERE scbStock.supplier_id = '.$supplierId.'
                            AND scbStock.brand_id = p.brand_id
                            AND scbStock.category_id = p.category_id) stockAvailable')
                    ->addSelect('(SELECT saleScb.saleAvailable
                            FROM Model_Entity_SupplierCategoryBrand saleScb
                            WHERE saleScb.supplier_id = '.$supplierId.'
                            AND saleScb.brand_id = p.brand_id
                            AND saleScb.category_id = p.category_id) saleAvailable')
                    ->from('Model_Entity_Product p')
                    ->leftJoin('p.Category category')
                    ->leftJoin('category.Translation categoryTranslate')
                    ->where('categoryTranslate.lang = ?', Zend_Auth::getInstance()->getIdentity()->language)
                    ->leftJoin('p.Brand brand')
                    ->orderBy($this->_helper->sort($mapping));

        
        // Where Conditions
        if (array_key_exists('categories', $params)) {
            $query->whereIn('product.category_id', $params['categories']);
        }

        // Pager
        $pager = $this->_helper->pagination($query);
        $responseData = $pager->execute()->toArray();

        // Response
        $this->getResponse()
                ->setHttpResponseCode(200)
                ->appendBody(
            $this->_helper->response()
                    ->setSuccess(true)
                    ->addTotal($pager->getNumResults())
                    ->addData($responseData)
                    ->getResponse()
        );

    }