Skip to content

Magento Custom Attribute Sorting – By Sort Order

I would like to get the catalog to sort by the custom attribute sort_order position.

However, when using custom attributes to sort product collections, in MYSQL it sorts by attribtute value name, when using ElasticSearch it sorts by attribute value id.

On Magento 1 I had put in place the ability to sort by attribute value sort_order and I’m looking to bring this to Magento 2, however as of right now the collection returned is always the same.

if ($subject->getCurrentOrder() &&  $subject->getCurrentOrder() != 'price' && $subject->getCurrentOrder() != 'relevance') {

            if (count($subject->getCollection()->getAllIds()) > 0) {

                $attributeId = $this->_eavAttribute->getIdByCode('catalog_product', $subject->getCurrentOrder());

                 $products = $this->_productCollectionFactory->create()
                    ->addStoreFilter()
                    ->addAttributeToSelect('status')
                    ->addAttributeToSelect('entity_id')
                    ->addAttributeToSelect($subject->getCurrentOrder())
                    ->setPageSize($subject->getProductsLimit())
                    ->addAttributeToFilter('status', 1)
                    ->addFieldToFilter('entity_id', array('in' => array($subject->getCollection()->getAllIds())))->load();

                $products->clear()
->reset(Zend_Db_Select::ORDER)
                    ->getSelect()
                    ->joinLeft(array('cpei' => 'catalog_product_entity_int'), 'e.entity_id = cpei.entity_id AND cpei.attribute_id = ' . $attributeId . ' AND cpei.store_id = 0', 'cpei.value as product_on_top')
                    ->joinLeft(array('eao' => 'eav_attribute_option'), 'eao.option_id = cpei.value', 'eao.sort_order as sort_order')
                    ->order('sort_order' . ' ' . 'desc');

                $result->getCollection()->getSelect()->order(
                    new Zend_Db_Expr('FIELD(`e`.`entity_id`,' . implode(
                            ',', array_unique($products->getAllIds())
                        ) . ') '
                    ));
            }
        }

        return $result;

Achieving this same functionality in Magento 2 is proving painful.

A) Should I be looking at beforeSetCollection, aroundSetCollection, afterSetCollection or overridding setCollection to influence both MYSQL and ElasticSearch?

B) This code is no longer influencing the sort order of the product collection