Magento 2 – Fix Product Collection Sort Order by Custom SKUs with Hyphens

magento2productproduct-collectionsku

Product collection sort order by skus could use $collection->getSelect()->order(new /Zend_Db_Expr("FIELD(e.sku, $skus)")); (Magento 2: Order product collection by the order string(skus, ids etc) given, not DESC or ASC), but when sku has a hyphen in it, it will result a error like below.

Example:

$skus = "24-MB04,24-MB01";
$collection->addAttributeToSelect('*')
        ->addAttributeToFilter(
            'sku', array('in' => $skus)
        );
$collection->getSelect()->order(new /Zend_Db_Expr("FIELD(e.sku, $skus)"));

Result:

Error filtering template: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'MB04' in 'order clause', query was: SELECT `e`.*, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e` LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 WHERE (`e`.`sku` IN('24-MB04', '24-MB01')) ORDER BY FIELD(sku, 24-MB04,24-MB01)

Obviously, the hyphen(-) in the sku(24-MB04) result the Unknown column 'MB04' in 'order clause' error, because when the sku doesn't have hyphen(-) in it, the codes work well, so how to fix the problem?

Best Answer

After a research, finally find the solution, we need process the $skus string to fix the syntax error in database query.

$skus = "'" . str_replace(",", "','", $skus) . "'";
$collection->getSelect()->order(new \Zend_Db_Expr("FIELD(sku,$skus)"));

Reference: Add quotes to values in a string separated by a comma php

Related Topic