1

I have a custom attribute (text field) - let's call it "size". It contains numeric values like 1,2,3,10,20,30. Products are sorted by this attribute on the category page.

Expected sort order:

1,2,3,10,20,30

Actual sort order:

1,10,2,20,3,30

I found this general answer which describes a general solution (not Magento related): https://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly

There seems to be a solution for Magento 1.9: https://stackoverflow.com/questions/22260419/magento-sort-attribute-by-decimal-not-alphanumerically

So here is my question: How can I achieve a natural sort order for attributes that contain numbers in Magento 2 on the category page?

1

By default, if you don't specify it in the attribute, text attributes allow different types of characters:

Since strings are allowed, Magento's sort query is as follows:

ORDER BY custom_order ASC, `e`.`entity_id` DESC
 LIMIT 12;

And the sort order of this would be: 1,10,2,20,3,30

On the other hand, if we indicate in the attribute configuration:

Advanced Attribute Properties->Input Validation for Store Owner: Integer number enter image description here

The sorting query would automatically be as follows:

ORDER BY CAST(custom_order AS SIGNED) ASC, `e`.`entity_id` DESC
 LIMIT 12;

And the sort order would be correct: 10,15,20,20,100

Conclusion: Change Advanced Attribute Properties->Input Validation for Store Owner: to "Integer number" in the attribute configuration.

2
  • This is the solution! It works! Never thought about this. Thank you! Please upvote this answer!
    – Stefan
    Feb 5 at 9:19
  • You're welcome. I was fighting with this for a whole day, heheheh
    – raumatbel
    Feb 5 at 9:27
0

And why you are sorting via string in the first place? I think that the most valid approach will be for you to change the column to int.

I done a quick check, and it looks like this is how things are done by MYSQL, so I think you can't do much about it, expect changing the column type.

To do that, you either must make an upgrade with upgradeSchema script or Declaratvie Schema.

By the way!!!!

I think you are trying to order your products on category page, right ?

This is an OOTB functionality

Just go to the

ADMIN -> Catalog -> Categories -> Your desired category -> Products in category tab

There you will have list of products, each of them has something like position, and this is responsible for positioning the element in the position search.

4
  • I'm using a string because Magento 2 does not allow to create attributes with numeric fields. Or am I missing something? Is it really a good practice to change the DB column type in Magento? I bet there must be some other way, right? Thanks for your answer btw.
    – Stefan
    Jan 10 '20 at 8:22
  • Kinda, it seems that it's not possible via magento admin panel. It's possible via installation scripts. Jan 10 '20 at 8:36
  • Here is an example on how to do it. mageplaza.com/magento-2-module-development/… Just change the type based on your needs. I think that int will suit your problem. Jan 10 '20 at 8:38
  • @Stefan I updated the answer, I think it may interest, unless you looking for a full custom solution. Jan 10 '20 at 8:50
0

I have gone through the slimier issue for custom collection and I have used the following code to fix this.

$_productCollection->getSelect()->reset('order');
$_productCollection->getSelect()->order('CAST(`size` AS SIGNED) ASC');

Hope it'll solve the issue. Cheers!

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.