1

how can I get products where the value of a multiselect is not set ?

I've got these so far :

$filter =
    array(
      array('eq' => array("378")), //multiselect with one value
      array('eq' => array("378,379")), //multiselect with 2 values
      array('eq' => false), //multiselect with no value, need to make this work
);

$collection->addAttributeToFilter( "multiselect", $filter );
0

Try this:

$filter =
    array(
      array('eq' => array("378")), // multiselect with one value
      array('eq' => array("378,379")), // multiselect with 2 values
      array('null' => true), // multiselect with no value (null value)
);

$collection->addAttributeToFilter( "multiselect", $filter );

Update:

here is how it will work using the SearchCriteria with the product repository (tested on the custom attribute available_shipping_methods having a multiselect input type):

use Magento\Catalog\Api\ProductRepositoryInterface;
use Magento\Framework\Api\FilterBuilder;
use Magento\Framework\Api\Search\FilterGroupBuilder;
use Magento\Framework\Api\SearchCriteriaBuilderFactory;
use Magento\Framework\App\Action\Action;
use Magento\Framework\App\Action\Context;
use Magento\Framework\App\ResponseInterface;

/**
 * Class Search
 */
class Search extends Action
{
    /**
     * @var ProductRepositoryInterface
     */
    private $productRepository;

    /**
     * @var SearchCriteriaBuilderFactory
     */
    private $searchCriteriaBuilderFactory;

    /**
     * @var FilterBuilder
     */
    private $filterBuilder;

    /**
     * @var FilterGroupBuilder
     */
    private $filterGroupBuilder;

    /**
     * Search constructor.
     *
     * @param Context $context
     * @param ProductRepositoryInterface $productRepository
     * @param SearchCriteriaBuilderFactory $searchCriteriaBuilderFactory
     * @param FilterBuilder $filterBuilder
     * @param FilterGroupBuilder $filterGroupBuilder
     */
    public function __construct(
        Context $context,
        ProductRepositoryInterface $productRepository,
        SearchCriteriaBuilderFactory $searchCriteriaBuilderFactory,
        FilterBuilder $filterBuilder,
        FilterGroupBuilder $filterGroupBuilder
    ) {
        parent::__construct($context);
        $this->productRepository            = $productRepository;
        $this->searchCriteriaBuilderFactory = $searchCriteriaBuilderFactory;
        $this->filterBuilder                = $filterBuilder;
        $this->filterGroupBuilder           = $filterGroupBuilder;
    }

    /**
     * Execute action based on request and return result
     *
     * Note: Request will be added as operation argument in future
     *
     * @return \Magento\Framework\Controller\ResultInterface|ResponseInterface
     * @throws \Magento\Framework\Exception\NotFoundException
     */
    public function execute()
    {
        try {
            $searchCriteriaBuilder = $this->searchCriteriaBuilderFactory->create();

            $filters = [
                $this->filterBuilder->setField('available_shipping_methods')
                                    ->setValue('%shipping_shipping%')
                                    ->setConditionType('like')
                                    ->create(),
                $this->filterBuilder->setField('available_shipping_methods')
                                    ->setValue('%flatrate_flatrate%')
                                    ->setConditionType('like')
                                    ->create(),
                $this->filterBuilder->setField('available_shipping_methods')
                                    ->setValue(null)
                                    ->setConditionType('null')
                                    ->create()
            ];

            $filterGroup = $this->filterGroupBuilder->setFilters($filters)
                                                    ->create();

            $searchCriteriaBuilder->setFilterGroups([$filterGroup]);
            $searchCriteria = $searchCriteriaBuilder->create();

            $productList = $this->productRepository->getList($searchCriteria);
        } catch (\Exception $e) {
            $this->messageManager->addErrorMessage($e->getMessage());
        }

        $resultPage = $this->resultFactory->create($this->resultFactory::TYPE_PAGE);

        return $resultPage;
    }
}

Result query looks like this (our conditions at the last line):

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`, IF(at_available_shipping_methods.value_id > 0, at_available_shipping_methods.value, at_available_shipping_methods_default.value) AS `available_shipping_methods`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`row_id` = `e`.`row_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`.`row_id` = `e`.`row_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`.`row_id` = `e`.`row_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`.`row_id` = `e`.`row_id`) AND (`at_visibility`.`attribute_id` = '99') AND (`at_visibility`.`store_id` = 1)
 INNER JOIN `catalog_product_entity_text` AS `at_available_shipping_methods_default` ON (`at_available_shipping_methods_default`.`row_id` = `e`.`row_id`) AND (`at_available_shipping_methods_default`.`attribute_id` = '265') AND `at_available_shipping_methods_default`.`store_id` = 0
 LEFT JOIN `catalog_product_entity_text` AS `at_available_shipping_methods` ON (`at_available_shipping_methods`.`row_id` = `e`.`row_id`) AND (`at_available_shipping_methods`.`attribute_id` = '265') AND (`at_available_shipping_methods`.`store_id` = 1)
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id WHERE (((IF(at_available_shipping_methods.value_id > 0, at_available_shipping_methods.value, at_available_shipping_methods_default.value) LIKE '%shipping_shipping%') OR (IF(at_available_shipping_methods.value_id > 0, at_available_shipping_methods.value, at_available_shipping_methods_default.value) LIKE '%flatrate_flatrate%') OR (IF(at_available_shipping_methods.value_id > 0, at_available_shipping_methods.value, at_available_shipping_methods_default.value) IS NULL))) AND (e.created_in <= 1) AND (e.updated_in > 1)

and result itself contains products with desired attribute value and without value at all (null).

PS: a comma-separated value of the multiselect attribute is the main problem, because of that we need to use %LIKE% condition, what is not a good idea.

  • Ok I'll try, but I think I'll just add the two options for each of my concerned products (like 2 options == 0 options) – Morgan Tartreau Apr 9 at 12:41

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.