Ttdfjt

Replacing 5 gang light switches that have 3 of them daisy chained together

What verb goes with "coup"?

Why are symbols not written in words?

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

How soon after takeoff can you recline your airplane seat?

*p++->str : Understanding evaluation of ->

How to idiomatically express the idea "if you can cheat without being caught, do it"

How far can gerrymandering go?

What is the point of using the kunai?

Is it OK to throw pebbles and stones in streams, waterfalls, ponds, etc.?

How do I tell my girlfriend she's been buying me books by the wrong author for the last nine months?

What is the meaning of "it" in "as luck would have it"?

Finding an optimal set without forbidden subsets

Find the closest three-digit hex colour

What is the meaning of ゴト in the context of 鮎

Why can't i use !(single pattern) in zsh even after i turn on kshglob?

Can I deep fry food in butter instead of vegetable oil?

What does this Pokemon Trainer mean by saying the player is "SHELLOS"?

Simplify the code

Existence of infinite set of positive integers s.t sum of reciprocals is rational and set of primes dividing an element is infinite

How come having a Deathly Hallow is not a big deal?

Displace Mesh along Curve on single axis

How to track mail undetectably?

Are the Gray and Death Slaad's Bite and Claw attacks magical?

Magento 2.1.10: Sort by Special Price doesn't working

Export from the database brand, quantity and size/color varchar: MySQL & EEConvert SQL Select to Magento SQL querySubselect as main_table on magento collectioni want to write custom sql query to select low and high price in magento2?Indexer query error empty valuesHow to sort by popularity in magento 2 product list?Best seller sort order pagination query string not working correctlyMagento 1.9 - Product collection sort by price not working fineMagento Product Listing Sort By not working on some categoryCategory page take too much time to load because of search_tmp_* table

2

I'm working on category page, and try to sort the products collection by Special Price.

I've override setCollection() of MagentoCatalogBlockProductProductListToolbar

And the result is the only sort for Normal price (not including Special Price):

So my solution is Left Join with catalog_product_entity_decimal table (this table is using for store Special Price, also store Normal Price as well, 149.0000 is normal price, and 120.0000 is special price).

And I print the query, and got this:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `catalog_product_entity_decimal`.`value` AS `special_price` 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.visibility IN(2, 4) AND cat_index.category_id='127'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 LEFT JOIN `catalog_product_entity_decimal` ON e.entity_id = catalog_product_entity_decimal.entity_id WHERE (attribute_id = 78 or attribute_id = 77) AND (catalog_product_entity_decimal.store_id = 0) GROUP BY `entity_id` ORDER BY `special_price` desc, `price` desc

But with this, the GROUP BY affected before ORDER BY, and ruin my result.

Its remove the 120.0000 and only show the 149.0000 in query result

If I remove the GROUP BY, it will show both, but the Magento code will error with "Already exists Entity Id".

If there is any way to make GROUP BY go after ORDER BY? Or better way to solve the problems?
Thanks.

magento-2.1 query product-sorting

share|improve this question

edited Jun 24 at 11:26

Raj Mohan R

1,19333 silver badges1111 bronze badges

asked Jun 24 at 11:03

fudufudu

4781414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think there is no need for coding. You can add sort by special price in frontend by simply changing Used for Sorting in Product Listing value to Yes in special_price attribute.
  
  – Mohit Kumar Arora
  Jun 24 at 11:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @MohitKumarArora i've tried as you said, but its not working, i've clear cache as well.
  
  – fudu
  Jun 24 at 11:25
  

  
  
  
  

add a comment | 

2

I'm working on category page, and try to sort the products collection by Special Price.

I've override setCollection() of MagentoCatalogBlockProductProductListToolbar

And the result is the only sort for Normal price (not including Special Price):

So my solution is Left Join with catalog_product_entity_decimal table (this table is using for store Special Price, also store Normal Price as well, 149.0000 is normal price, and 120.0000 is special price).

And I print the query, and got this:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `catalog_product_entity_decimal`.`value` AS `special_price` 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.visibility IN(2, 4) AND cat_index.category_id='127'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 LEFT JOIN `catalog_product_entity_decimal` ON e.entity_id = catalog_product_entity_decimal.entity_id WHERE (attribute_id = 78 or attribute_id = 77) AND (catalog_product_entity_decimal.store_id = 0) GROUP BY `entity_id` ORDER BY `special_price` desc, `price` desc

But with this, the GROUP BY affected before ORDER BY, and ruin my result.

Its remove the 120.0000 and only show the 149.0000 in query result

If I remove the GROUP BY, it will show both, but the Magento code will error with "Already exists Entity Id".

If there is any way to make GROUP BY go after ORDER BY? Or better way to solve the problems?
Thanks.

magento-2.1 query product-sorting

share|improve this question

edited Jun 24 at 11:26

Raj Mohan R

1,19333 silver badges1111 bronze badges

asked Jun 24 at 11:03

fudufudu

4781414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think there is no need for coding. You can add sort by special price in frontend by simply changing Used for Sorting in Product Listing value to Yes in special_price attribute.
  
  – Mohit Kumar Arora
  Jun 24 at 11:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @MohitKumarArora i've tried as you said, but its not working, i've clear cache as well.
  
  – fudu
  Jun 24 at 11:25
  

  
  
  
  

add a comment | 

I'm working on category page, and try to sort the products collection by Special Price.

I've override setCollection() of MagentoCatalogBlockProductProductListToolbar

And the result is the only sort for Normal price (not including Special Price):

So my solution is Left Join with catalog_product_entity_decimal table (this table is using for store Special Price, also store Normal Price as well, 149.0000 is normal price, and 120.0000 is special price).

And I print the query, and got this:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `catalog_product_entity_decimal`.`value` AS `special_price` 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.visibility IN(2, 4) AND cat_index.category_id='127'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 LEFT JOIN `catalog_product_entity_decimal` ON e.entity_id = catalog_product_entity_decimal.entity_id WHERE (attribute_id = 78 or attribute_id = 77) AND (catalog_product_entity_decimal.store_id = 0) GROUP BY `entity_id` ORDER BY `special_price` desc, `price` desc

But with this, the GROUP BY affected before ORDER BY, and ruin my result.

Its remove the 120.0000 and only show the 149.0000 in query result

If I remove the GROUP BY, it will show both, but the Magento code will error with "Already exists Entity Id".

If there is any way to make GROUP BY go after ORDER BY? Or better way to solve the problems?
Thanks.

magento-2.1 query product-sorting

share|improve this question

edited Jun 24 at 11:26

Raj Mohan R

1,19333 silver badges1111 bronze badges

asked Jun 24 at 11:03

fudufudu

4781414 bronze badges

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `catalog_product_entity_decimal`.`value` AS `special_price` 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.visibility IN(2, 4) AND cat_index.category_id='127'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 LEFT JOIN `catalog_product_entity_decimal` ON e.entity_id = catalog_product_entity_decimal.entity_id WHERE (attribute_id = 78 or attribute_id = 77) AND (catalog_product_entity_decimal.store_id = 0) GROUP BY `entity_id` ORDER BY `special_price` desc, `price` desc

share|improve this question

edited Jun 24 at 11:26

Raj Mohan R

1,19333 silver badges1111 bronze badges

asked Jun 24 at 11:03

fudufudu

4781414 bronze badges

share|improve this question

edited Jun 24 at 11:26

Raj Mohan R

1,19333 silver badges1111 bronze badges

asked Jun 24 at 11:03

fudufudu

4781414 bronze badges

edited Jun 24 at 11:26

Raj Mohan R

1,19333 silver badges1111 bronze badges

edited Jun 24 at 11:26

Raj Mohan R

1,19333 silver badges1111 bronze badges

asked Jun 24 at 11:03

fudufudu

4781414 bronze badges

asked Jun 24 at 11:03

fudufudu

4781414 bronze badges