public function getProductsByCategoryId($categoryId, $filterByNewProducts = false, $keyword = '', $productId = 0, $countMode = false)
    {
        $params = [
            'active' => APP_ON
        ];
        if (! $this->getLoggedUser()) {
            $params['isPrivate'] = APP_OFF;
        }

        $sql = 'SELECT ';
        $sql .= $this->getFieldsForProductListQuery();
        $sql .= "FROM ".$this->tablePrefix."product Products ";

        if (! $filterByNewProducts) {
            $sql .= "LEFT JOIN ".$this->tablePrefix."category_product CategoryProducts ON CategoryProducts.id_product = Products.id_product
                 LEFT JOIN ".$this->tablePrefix."category Categories ON CategoryProducts.id_category = Categories.id_category ";
        }

        $sql .= $this->getJoinsForProductListQuery();
        $sql .= $this->getConditionsForProductListQuery();

        if (! $filterByNewProducts) {
            $params['categoryId'] = $categoryId;
            $sql .= " AND CategoryProducts.id_category = :categoryId ";
            $sql .= " AND Categories.active = :active";
        }

        if ($filterByNewProducts) {
            $params['dateAdd'] = date('Y-m-d', strtotime('-' . Configure::read('appDb.FCS_DAYS_SHOW_PRODUCT_AS_NEW') . ' DAYS'));
            $sql .= " AND DATE_FORMAT(Products.created, '%Y-%m-%d') > :dateAdd";
        }

        if ($keyword != '') {
            $params['keyword'] = '%' . $keyword . '%';
            $sql .= " AND (Products.name LIKE :keyword OR Products.description_short LIKE :keyword) ";
        }

        if ($productId > 0) {
            $params['productId'] = $productId;
            $sql .= " AND Products.id_product = :productId ";
        }

        $sql .= $this->getOrdersForProductListQuery();
        $statement = $this->getConnection()->prepare($sql);
        $statement->execute($params);
        $products = $statement->fetchAll('assoc');
        $products = $this->hideProductsWithActivatedDeliveryRhythmOrDeliveryBreak($products);

        if (! $countMode) {
            return $products;
        } else {
            return count($products);
        }

        return $products;
    }
public function getProductsByCategoryId($categoryId, $filterByNewProducts = false, $keyword = '', $productId = 0, $countMode = false)
    {
        $params = [
            'active' => APP_ON
        ];
        if (! $this->getLoggedUser()) {
            $params['isPrivate'] = APP_OFF;
        }

        $sql = 'SELECT ';
        $sql .= $this->getFieldsForProductListQuery();
        $sql .= "FROM ".$this->tablePrefix."product Products ";

        if (! $filterByNewProducts) {
            $sql .= "LEFT JOIN ".$this->tablePrefix."category_product CategoryProducts ON CategoryProducts.id_product = Products.id_product
                 LEFT JOIN ".$this->tablePrefix."category Categories ON CategoryProducts.id_category = Categories.id_category ";
        }

        $sql .= $this->getJoinsForProductListQuery();
        $sql .= $this->getConditionsForProductListQuery();

        if (! $filterByNewProducts) {
            $params['categoryId'] = $categoryId;
            $sql .= " AND CategoryProducts.id_category = :categoryId ";
            $sql .= " AND Categories.active = :active";
        }

        if ($filterByNewProducts) {
            $params['dateAdd'] = date('Y-m-d', strtotime('-' . Configure::read('appDb.FCS_DAYS_SHOW_PRODUCT_AS_NEW') . ' DAYS'));
            $sql .= " AND DATE_FORMAT(Products.created, '%Y-%m-%d') > :dateAdd";
        }

        if ($keyword != '') {
            $params['keyword'] = '%' . $keyword . '%';
            $sql .= " AND (Products.name LIKE :keyword OR Products.description_short LIKE :keyword) ";
        }

        if ($productId > 0) {
            $params['productId'] = $productId;
            $sql .= " AND Products.id_product = :productId ";
        }

        $sql .= $this->getOrdersForProductListQuery();
        $statement = $this->getConnection()->prepare($sql);
        $statement->execute($params);
        $products = $statement->fetchAll('assoc');
        $products = $this->hideProductsWithActivatedDeliveryRhythmOrDeliveryBreak($products);

        if (! $countMode) {
            return $products;
        } else {
            return count($products);
        }

        return $products;
    }
public function getProductsByCategoryId ( $ categoryId , $ filterByNewProducts = false , $ keyword = '' , $ productId = 0 , $ countMode = false ) { $ params = [ 'active' => APP_ON ] ; if ( ! $ this -> getLoggedUser ( ) ) { $ params [ 'isPrivate' ] = APP_OFF ; } $ sql = 'SELECT ' ; $ sql .= $ this -> getFieldsForProductListQuery ( ) ; $ sql .= "FROM " . $ this -> tablePrefix . "product Products " ; if ( ! $ filterByNewProducts ) { $ sql .= "LEFT JOIN " . $ this -> tablePrefix . "category_product CategoryProducts ON CategoryProducts.id_product = Products.id_product
                 LEFT JOIN " . $ this -> tablePrefix . "category Categories ON CategoryProducts.id_category = Categories.id_category " ; } $ sql .= $ this -> getJoinsForProductListQuery ( ) ; $ sql .= $ this -> getConditionsForProductListQuery ( ) ; if ( ! $ filterByNewProducts ) { $ params [ 'categoryId' ] = $ categoryId ; $ sql .= " AND CategoryProducts.id_category = :categoryId " ; $ sql .= " AND Categories.active = :active" ; } if ( $ filterByNewProducts ) { $ params [ 'dateAdd' ] = date ( 'Y-m-d' , strtotime ( '-' . Configure :: read ( 'appDb.FCS_DAYS_SHOW_PRODUCT_AS_NEW' ) . ' DAYS' ) ) ; $ sql .= " AND DATE_FORMAT(Products.created, '%Y-%m-%d') > :dateAdd" ; } if ( $ keyword != '' ) { $ params [ 'keyword' ] = '%' . $ keyword . '%' ; $ sql .= " AND (Products.name LIKE :keyword OR Products.description_short LIKE :keyword) " ; } if ( $ productId > 0 ) { $ params [ 'productId' ] = $ productId ; $ sql .= " AND Products.id_product = :productId " ; } $ sql .= $ this -> getOrdersForProductListQuery ( ) ; $ statement = $ this -> getConnection ( ) -> prepare ( $ sql ) ; $ statement -> execute ( $ params ) ; $ products = $ statement -> fetchAll ( 'assoc' ) ; $ products = $ this -> hideProductsWithActivatedDeliveryRhythmOrDeliveryBreak ( $ products ) ; if ( ! $ countMode ) { return $ products ; } else { return count ( $ products ) ; } return $ products ; }
custom sql for best performance
product attributes ARE NOT fetched in this query!
custom sql for best performance product attributes ARE NOT fetched in this query!