2

How can I programmatically get products that has an active special price? (by checking the end date).

1 Answer 1

4

Here is complete search criteria with 4 filters:

  1. $withSpecialPrice - a filter for products with a special price greater then 0
  2. $withVisibleStatus - a filter for product with a visible status (enabled products)
  3. $visibleInCatalog - a filter for products with a visibility catalog, search, catalog & search
  4. $specialPriceDate - a filter for products by special price date greater then current date or null (without specified end-date)

All conditions are concatenated by the AND (filter groups). Special-price-date conditions are concatenated by the OR with each other (filters inside one filter group).

Complete code:

/**
 * @param \Magento\Catalog\Api\ProductRepositoryInterface $productRepository
 * @param \Magento\Framework\Api\SearchCriteriaInterface $criteria
 * @param \Magento\Framework\Api\Search\FilterGroupBuilder $filterGroupBuilder
 * @param \Magento\Framework\Api\FilterBuilder $filterBuilder
 * @param \Magento\Catalog\Model\Product\Attribute\Source\Status $productStatus
 * @param \Magento\Catalog\Model\Product\Visibility $productVisibility
 */
public function __construct(
    \Magento\Catalog\Api\ProductRepositoryInterface $productRepository,
    \Magento\Framework\Api\SearchCriteriaInterface $criteria,
    \Magento\Framework\Api\Search\FilterGroupBuilder $filterGroupBuilder,
    \Magento\Framework\Api\FilterBuilder $filterBuilder,
    \Magento\Catalog\Model\Product\Attribute\Source\Status $productStatus,
    \Magento\Catalog\Model\Product\Visibility $productVisibility,
    \Magento\Framework\Stdlib\DateTime\DateTime $date
) {
    $this->productRepository = $productRepository;
    $this->searchCriteria = $criteria;
    $this->filterGroupBuilder = $filterGroupBuilder;
    $this->filterBuilder = $filterBuilder;
    $this->productStatus = $productStatus;
    $this->productVisibility = $productVisibility;
    $this->date = $date;
}

/**
 * @return \Magento\Cms\Model\Block|null
 * @throws \Magento\Framework\Exception\NoSuchEntityException
 */
public function getProductData()
{
    /** @var \Magento\Framework\Api\Search\FilterGroup $withSpecialPrice */
    $withSpecialPrice = $this->filterGroupBuilder->create();
    $withSpecialPrice->setFilters(
        [
            $this->filterBuilder
                ->setField('special_price')
                ->setConditionType('gt')
                ->setValue(0)
                ->create(),
        ]
    );

    /** @var \Magento\Framework\Api\Search\FilterGroup $withVisibleStatus */
    $withVisibleStatus = $this->filterGroupBuilder->create();
    $withVisibleStatus->setFilters(
        [
            $this->filterBuilder
                ->setField('status')
                ->setConditionType('in')
                ->setValue($this->productStatus->getVisibleStatusIds())
                ->create(),
        ]
    );

    /** @var \Magento\Framework\Api\Search\FilterGroup $visibleInCatalog */
    $visibleInCatalog = $this->filterGroupBuilder->create();
    $visibleInCatalog->setFilters(
        [
            $this->filterBuilder
                ->setField('visibility')
                ->setConditionType('in')
                ->setValue($this->productVisibility->getVisibleInSiteIds())
                ->create(),
        ]
    );

    /** @var \Magento\Framework\Api\Search\FilterGroup $specialPriceDate */
    $specialPriceDate = $this->filterGroupBuilder->create();
    $specialPriceDate->setFilters(
        [
           $this->filterBuilder
                ->setField('special_to_date')
                ->setConditionType('null')
                ->setValue('')
                ->create(),
            $this->filterBuilder
                ->setField('special_to_date')
                ->setConditionType('gt')
                ->setValue($this->date->gmtDate())
                ->create(),
        ]
    );

    $this->searchCriteria->setFilterGroups([$withSpecialPrice, $withVisibleStatus, $visibleInCatalog, $specialPriceDate]);
    $products = $this->productRepository->getList($this->searchCriteria);
    $productItems = $products->getItems();

    return $productItems;
}

Here is complete sql query string:

SELECT
  `e`.*,
  IF(at_status.value_id > 0, at_status.value, at_status_default.value)                            AS `status`,
  IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value)                AS `visibility`,
  `at_special_price`.`value`                                                                      AS `special_price`,
  IF(at_special_to_date.value_id > 0, at_special_to_date.value, at_special_to_date_default.value) AS `special_to_date`
FROM `catalog_product_entity` AS `e`
  INNER JOIN `catalog_product_entity_int` AS `at_status_default`
    ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '97') AND
       `at_status_default`.`store_id` = 0
  LEFT JOIN `catalog_product_entity_int` AS `at_status`
    ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '97') AND
       (`at_status`.`store_id` = 1)
  INNER JOIN `catalog_product_entity_int` AS `at_visibility_default`
    ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '99') AND
       `at_visibility_default`.`store_id` = 0
  LEFT JOIN `catalog_product_entity_int` AS `at_visibility`
    ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '99') AND
       (`at_visibility`.`store_id` = 1)
  INNER JOIN `catalog_product_entity_decimal` AS `at_special_price`
    ON (`at_special_price`.`entity_id` = `e`.`entity_id`) AND (`at_special_price`.`attribute_id` = '78') AND
       (`at_special_price`.`store_id` = 0)
  INNER JOIN `catalog_product_entity_datetime` AS `at_special_to_date_default` ON (
                                                                                    `at_special_to_date_default`.`entity_id`
                                                                                    = `e`.`entity_id`) AND (
                                                                                    `at_special_to_date_default`.`attribute_id`
                                                                                    = '80') AND
                                                                                  `at_special_to_date_default`.`store_id`
                                                                                  = 0
  LEFT JOIN `catalog_product_entity_datetime` AS `at_special_to_date`
    ON (`at_special_to_date`.`entity_id` = `e`.`entity_id`) AND (`at_special_to_date`.`attribute_id` = '80') AND
       (`at_special_to_date`.`store_id` = 1)
WHERE
  ((at_special_price.value > 0)) AND ((IF(at_status.value_id > 0, at_status.value, at_status_default.value) IN (1)))
  AND
  ((IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) IN (3, 2, 4)))
  AND
  (
    (IF(at_special_to_date.value_id > 0, at_special_to_date.value, at_special_to_date_default.value) IS NULL)
    OR
    (IF(at_special_to_date.value_id > 0, at_special_to_date.value, at_special_to_date_default.value) >
     '2017-08-09 12:34:13')
  );

PS: seems that Magento can not filter properly products when a special price date is not set. I'll recommend you to fill this data in all your products to be sure that filter is working properly.

4
  • @srg I'm glad to help you :) Aug 9, 2017 at 13:22
  • how i will add limit in this or random Aug 21, 2018 at 7:47
  • How to Get Product in frontend (Phtml) ?? Jun 7, 2019 at 10:38
  • i am wondering does this work for configurable product? Nov 24, 2020 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.