0

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

0

In Magento 2 this is configurable on the product attribute itself. Try check it in admin panel -> Stores -> Attributes -> Product -> [edit some attribute] -> Storefront properties tab -> Used for Sorting in Product Listing = Yes (depends on theme).

1
  • It's busted and doesn't actually work with elasticsearch. It's a confirmed issue on Github on the Magento respository. Also, I don't want to sort alphabetically, I want to search using the position of the attribute value
    – elfling
    Sep 1 at 12:13

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.