2

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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