carlos

    public function findByShopId($shopId, $categoryId, $orderBy, $page, $limit)
    {
        $rsm = $this->buildResultSetMapping(array(
            'offerId' => 'offerId',
            'productId' => 'productId'
        ));

        $query = $this->getDoctrine()
            ->getManager('legacy')
            ->createNativeQuery('\Shopbot\ShoppingBundle\Doctrine\Entity\Shopsearchindexsf', $rsm);

        $sql = '
SELECT
    offer.refshopcodeid, offer.refproduct
FROM
    webshop.dbo.shopsearchindexsf AS offer
LEFT JOIN
    shopenginesf AS product
ON
    product.refproduct = offer.refproduct
    AND product.countryCode = offer.countryCode
    AND product.result_type = :typeProduct
WHERE
    offer.countryCode = :country
    AND offer.refshop = :shopId
        ';

        if (!is_null($categoryId)) {
            $sql .= ' AND offer.reflevel2 = :categoryId';
            $query
                ->setParameter('categoryId', $categoryId);
        }

        switch ($orderBy) {
            case ShopController::OFFERS_SORT_POPULARITY:
                $sql .= ' ORDER BY offer.popularity DESC, offer.price ASC';
                break;
            case ShopController::OFFERS_SORT_DATE:
                $sql .= ' ORDER BY offer.refshopcodeid';
                break;
            case ShopController::OFFERS_SORT_PRICE:
                $sql .= ' ORDER BY offer.price ASC, offer.popularity DESC';
                break;
            case ShopController::OFFERS_SORT_NAME:
                $sql .= ' ORDER BY offer.productname ASC, offer.popularity DESC';
                break;
        }

        $query
            ->setSQL($sql)
            ->setParameter('country', $this->country)
            ->setParameter('typeProduct', Shopenginesf::RESULTTYPE_PRODUCT)
            ->setParameter('shopId', $shopId);

//        $query = $this->getDoctrine()
//            ->getManager('legacy')
//            ->createQueryBuilder()
//            ->select('offer')
//            ->addSelect('offer.shopId')
//            ->addSelect('offer.shopName')
//            ->addSelect('offer.offerId')
//            ->addSelect('offer.name')
//            ->addSelect('offer.slug')
//            ->addSelect('offer.productId')
//            ->addSelect('offer.universeId')
//            ->addSelect('offer.universeName')
//            ->addSelect('offer.level1Id')
//            ->addSelect('offer.level1Name')
//            ->addSelect('offer.level2Id')
//            ->addSelect('offer.level2Name')
//            ->addSelect('offer.level2Url')
//            ->addSelect('offer.platformId')
//            ->addSelect('offer.platformName')
//            ->addSelect('offer.popularity')
//            ->addSelect('offer.redirectionUrl')
//            ->addSelect('offer.photo90')
//            ->addSelect('offer.minPrice')
//            ->addSelect('offer.regularPrice')
//            ->addSelect('product.shopCount')
//            ->from('\Shopbot\ShoppingBundle\Doctrine\Entity\Shopsearchindexsf', 'offer')
//            ->leftJoin('Shopbot\ShoppingBundle\Doctrine\Entity\Shopenginesf', 'product', 'WITH', 'product.productId = offer.productId AND product.countryCode = offer.countryCode AND product.resultType = :typeProduct')
//            ->where('offer.countryCode = :country')
//            ->andWhere('offer.shopId = :shopId')
//            ->setParameter('country', $this->country)
//            ->setParameter('typeProduct', Shopenginesf::RESULTTYPE_PRODUCT)
//            ->setParameter('shopId', $shopId);
//
//        if (!is_null($categoryId)) {
//            $query
//                ->andWhere('offer.level2Id = :categoryId')
//                ->setParameter('categoryId', $categoryId);
//        }
//
//        switch ($orderBy) {
//            case ShopController::OFFERS_SORT_POPULARITY:
//                $query
//                    ->orderBy('offer.popularity', 'DESC')
//                    ->addOrderBy('offer.minPrice', 'ASC');
//
//                break;
//
//            case ShopController::OFFERS_SORT_DATE:
//                $query->orderBy('offer.offerId', 'DESC');
//
//                break;
//
//            case ShopController::OFFERS_SORT_PRICE:
//                $query
//                    ->orderBy('offer.minPrice', 'ASC')
//                    ->addOrderBy('offer.popularity', 'DESC');
//
//                break;
//
//            case ShopController::OFFERS_SORT_NAME:
//                $query
//                    ->orderBy('offer.name', 'ASC')
//                    ->addOrderBy('offer.popularity', 'DESC');
//
//                break;
//        }

        return $this->getPagination(
                $query
                    ->setHydrationMode(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY), $page, $limit
        );
    }