I have a problem in a product collection that I need to filter. I have a multiselect list with countries. When a selection is made the products need to be hidden from these countries.

$collection->addAttributeToFilter('available_not_for_countries', array(array('nin' => $billingCountry),array('null' => true)));

I know I need the opposite of finset like NOT FIND_IN_SET but I have no idea how to create the new Zend_Db_Expr for this in this filter.

  • What is the type of $billingCountry? Is it array? Or a string with comma-separated values? – Roman Snitko Nov 16 '18 at 11:25
  • available_not_for_countries is a multiselect attribute. $billingCountry is the default-billing address country of a logged in customer. So this can be "DE","ES" etc. – user1895954 Nov 16 '18 at 12:20

Try this one:

    ->where('NOT FIND_IN_SET(?, available_not_for_countries)', $billingCountry);
  • It's not working as $billingCountry is not comma seperated. But returns one value like "DE" or "BE". The attribute "available_not_for_countries" returns a multiselect of countries where products should not be visible. So based on the billingaddress country we know if we can show a product or not. – user1895954 Nov 16 '18 at 14:13
  • Okay, so $billingCountry is a string and "available_not_for_countries" is a comma-separated list of countries? eg. "DE,BE" ? – Roman Snitko Nov 16 '18 at 14:23
  • Yes indeed. As an example. I also have this filter and it works perfect: ->addAttributeToFilter('available_for_countries', array(array('finset' => $billingCountry),array('null' => true))); However the opposite of finset isn't available in Magento2 like you can see here: magento.stackexchange.com/questions/151142/… – user1895954 Nov 16 '18 at 16:22
  • Can not use the answer field. (someone help?) so here is the answer: I solved it thanks to the example of @Giel Beelen his feedback on: magento.stackexchange.com/questions/151142/…. My final result was: ->getSelect()->where( new \Zend_Db_Expr( sprintf('NOT FIND_IN_SET(%1$d, available_not_for_countries) OR available_not_for_countries IS NULL', $billingCountryNav ) ) ); – user1895954 Nov 18 '18 at 0:01

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.