Ttdfjt

Is leaving out prefixes like "rauf", "rüber", "rein" when describing movement considered a big mistake in spoken German?

Do electrons really perform instantaneous quantum leaps?

Does a lens with a bigger max. aperture focus faster than a lens with a smaller max. aperture?

Installed software from source, how to say yum not to install it from package?

What does 5d4 x 10 gp mean?

Why isn't UDP with reliability (implemented at Application layer) a substitute of TCP?

How do I present a future free of gender stereotypes without being jarring or overpowering the narrative?

I agreed to cancel a long-planned vacation (with travel costs) due to project deadlines, but now the timeline has all changed again

Why did the Apple //e make a hideous noise if you inserted the disk upside down?

Checkmate in 1 on a Tangled Board

Does friction always oppose motion?

How can an inexperienced GM keep a game fun for experienced players?

Fast method to cut/shred glue stick into small pieces

Fully submerged water bath for stove top baking?

Why was Pan Am Flight 103 flying over Lockerbie?

Example of query execution plan where the cardinality estimation makes a difference

How far can gerrymandering go?

Russian equivalents of 能骗就骗 (if you can cheat, then cheat)

What is the meaning of 'shout over' in a sentence exactly?

Can dual citizens open crypto exchange accounts where U.S. citizens are prohibited?

Avoiding repetition when using the "snprintf idiom" to write text

Dynamic Sql Query - how to add an int to the code?

Is it advisable to inform the CEO about his brother accessing his office?

Did the Russian Empire have a claim to Sweden? Was there ever a time where they could have pursued it?

Magento 2 product collection getSelect Order by Case unException Unknown column 'name' in 'order clause'

Magento sorting collection only sorting first 20 productsSQLSTATE[42S22]: Column not found: 1054 Unknown column 'product_id' in 'where clause'Getting custom attribute value from simple product load based on product collectionSQLSTATE[42S22]: Column not found: 1054 Unknown column 'attr_index_245.value' in 'having clause'Column not found: 1054 Unknown column 'ca.sort_order' in 'order clause' error“random” order clause added to my collectionProblem with SQLSTATE[42S22]: Column not foundMagento 2.2.6: Product collection does not apply the filterGet Top Rated Products and Order by ratingCategory page not working Magento 2 with more than 13 million products

0

I need to use ORDER BY CASE name which is product name

Query:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, 
`stock_status_index`.`stock_status` AS `is_salable` 
FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` 
ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 
AND cat_index.category_id='3'
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 
ORDER BY CASE WHEN `name` LIKE '%banana%' THEN 1 
WHEN `name` LIKE '%apple%' THEN 2 
ELSE 3 END

...
$collection->addCategoryFilter($category)
$collection->getSelect()->order(new Zend_Db_Expr("CASE WHENnameLIKE '%banana%' THEN 1 WHENnameLIKE '%apple%' THEN 2 ELSE 3 END"));
...

Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name' in 'order clause'

If do setOrder instead of order then it Works but it just order alphabetically like so:

$collection->addCategoryFilter($category)->setOrder('name', 'ASC');

I need Expression what is going wrong?

database collection product-collection magento2.2.2

share|improve this question

asked Jun 21 at 19:36

Juliano VargasJuliano Vargas

71266 silver badges2626 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  because of name has not alias of table name so you need to specify [alias].name in setOrder().
  
  – Hardik Visa
  Jun 22 at 6:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @HardikVisa SELECT e.*, cat_index.position` AS cat_index_position, stock_status_index.stock_status AS is_salable FROM catalog_product_entity AS e INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id='3' 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 ORDER BY CASE WHEN e.name LIKE '%banana%' THEN 1 WHEN e.name LIKE '%apple%' THEN 2 ELSE 3 END`
  
  – Juliano Vargas
  Jun 24 at 8:38
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @HardikVisa Doesn't work Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.name' in 'order clause'
  
  – Juliano Vargas
  Jun 24 at 8:38
  

  
  
  
  

add a comment | 

0

I need to use ORDER BY CASE name which is product name

Query:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, 
`stock_status_index`.`stock_status` AS `is_salable` 
FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` 
ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 
AND cat_index.category_id='3'
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 
ORDER BY CASE WHEN `name` LIKE '%banana%' THEN 1 
WHEN `name` LIKE '%apple%' THEN 2 
ELSE 3 END

...
$collection->addCategoryFilter($category)
$collection->getSelect()->order(new Zend_Db_Expr("CASE WHENnameLIKE '%banana%' THEN 1 WHENnameLIKE '%apple%' THEN 2 ELSE 3 END"));
...

Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name' in 'order clause'

If do setOrder instead of order then it Works but it just order alphabetically like so:

$collection->addCategoryFilter($category)->setOrder('name', 'ASC');

I need Expression what is going wrong?

database collection product-collection magento2.2.2

share|improve this question

asked Jun 21 at 19:36

Juliano VargasJuliano Vargas

71266 silver badges2626 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  because of name has not alias of table name so you need to specify [alias].name in setOrder().
  
  – Hardik Visa
  Jun 22 at 6:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @HardikVisa SELECT e.*, cat_index.position` AS cat_index_position, stock_status_index.stock_status AS is_salable FROM catalog_product_entity AS e INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id='3' 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 ORDER BY CASE WHEN e.name LIKE '%banana%' THEN 1 WHEN e.name LIKE '%apple%' THEN 2 ELSE 3 END`
  
  – Juliano Vargas
  Jun 24 at 8:38
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @HardikVisa Doesn't work Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.name' in 'order clause'
  
  – Juliano Vargas
  Jun 24 at 8:38
  

  
  
  
  

add a comment | 

I need to use ORDER BY CASE name which is product name

Query:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, 
`stock_status_index`.`stock_status` AS `is_salable` 
FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` 
ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 
AND cat_index.category_id='3'
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 
ORDER BY CASE WHEN `name` LIKE '%banana%' THEN 1 
WHEN `name` LIKE '%apple%' THEN 2 
ELSE 3 END

...
$collection->addCategoryFilter($category)
$collection->getSelect()->order(new Zend_Db_Expr("CASE WHENnameLIKE '%banana%' THEN 1 WHENnameLIKE '%apple%' THEN 2 ELSE 3 END"));
...

Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name' in 'order clause'

If do setOrder instead of order then it Works but it just order alphabetically like so:

$collection->addCategoryFilter($category)->setOrder('name', 'ASC');

I need Expression what is going wrong?

database collection product-collection magento2.2.2

share|improve this question

asked Jun 21 at 19:36

Juliano VargasJuliano Vargas

71266 silver badges2626 bronze badges

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, 
`stock_status_index`.`stock_status` AS `is_salable` 
FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` 
ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 
AND cat_index.category_id='3'
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 
ORDER BY CASE WHEN `name` LIKE '%banana%' THEN 1 
WHEN `name` LIKE '%apple%' THEN 2 
ELSE 3 END

share|improve this question

asked Jun 21 at 19:36

Juliano VargasJuliano Vargas

71266 silver badges2626 bronze badges

share|improve this question

asked Jun 21 at 19:36

Juliano VargasJuliano Vargas

71266 silver badges2626 bronze badges

asked Jun 21 at 19:36

Juliano VargasJuliano Vargas

71266 silver badges2626 bronze badges

asked Jun 21 at 19:36

Juliano VargasJuliano Vargas

71266 silver badges2626 bronze badges