I have added an extra sort by option to my magento 2 store which uses the price divided by a unit_of_sale attribute to sort the catalog by an each price.

The sort works in a lot of categories from what i can tell however some show no or less products when sorted by this option:

Code for the collection below:

case 'each_price':
                if ( $this->getCurrentDirection() == 'desc' ) {
                        ->addAttributeToSelect('unit_of_sale', 'catalog_product_entity_varchar')            
                        ->order(new \Zend_Db_Expr('price_index.min_price / IF(unit_of_sale = NULL, 1, unit_of_sale) DESC'));
                } elseif ( $this->getCurrentDirection() == 'asc' ) {
                        ->addAttributeToSelect('unit_of_sale', 'catalog_product_entity_varchar')                            
                        ->order(new \Zend_Db_Expr('price_index.min_price / IF(unit_of_sale = NULL, 1, unit_of_sale) ASC'));


Products that have no unit_of_sale set set are removed from the collection. How can i add the attribute so that I can run this simple calculation for sort but without removing items where unit_of_sale are null?


You need to change expression from

IF(unit_of_sale = NULL, 1, unit_of_sale)



because in MySQL NULL != NULL

  • Thanks for that still seems to be behaving the same way tho with some categories just showing no products with this sort. Categories that work do show products with unit of sale null have done in both cases so that's why i'm confused. – harri Oct 27 '17 at 7:36
  • I think my problem is the null thing. It's just this doesn't seem to fix it for some reason. If they are null they just aren't included in the collection. – harri Oct 27 '17 at 17:14

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.