2

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?

1

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

0

Try this:

$collection->addAttributeToFilter('sku', array('eq' => $sku));

Also if you want to select SKU's that are looks like your:

$collection->addAttributeToFilter('sku', array('like' => '%'.$sku.'%'));
  • ASC or DESC order is not my need, what I need is sort order by custom skus string, you could see the example I given, – Key Shang Sep 1 '17 at 9:13
  • Sorry for misunderstanding, try this with your collection. I've edited above answer. – Obscuro Sep 1 '17 at 9:15
  • You still mistake my question, the problem is not the ->addAttributeToFilter, the problem is at the $collection->getSelect()->order(new /Zend_Db_Expr("FIELD(e.sku, $skus)"));, you could see the more detailed codes I given :) – Key Shang Sep 1 '17 at 9:23
  • Oh sorry... I just can't get what do you want as a result. – Obscuro Sep 1 '17 at 9:27
  • As I see you're trying to filter by column named as your SKU's, but there are, for sure, no columns named like that: "ORDER BY FIELD(sku, 24-MB04,24-MB01)" – Obscuro Sep 1 '17 at 9:31

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.