Programatically get special price products that are active by checking the end date?

Question

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

Answer 1

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.