How can I programmatically get products that has an active special price? (by checking the end date).
1 Answer
Here is complete search criteria with 4 filters:
$withSpecialPrice
- a filter for products with a special price greater then 0$withVisibleStatus
- a filter for product with a visible status (enabled products)$visibleInCatalog
- a filter for products with a visibility catalog, search, catalog & search$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.