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