See attached. This is a very wrong way to sort these numbers.

CommentFileSizeAuthor
#15 orderfail.png51.5 KBkattekrab
order_method.PNG7.64 KBkevin.klika
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

googletorp’s picture

I'm going to say working as designed, but I'll let Ryan close the issue.

The thing is that the Order number is not a "number" but a text string. By default it will be a number, but you could store order numbers like

GIFT-2234
RIT-234

etc.

When sorting a text string each char is evaluated so 9 is the highest. You could display the order id instead which is an integer and the sorting would be as you expected.

bojanz’s picture

Status: Active » Closed (works as designed)

Confirming what googletorp said.

wzoom’s picture

I needed decimal Order Numbers too.
You can simply add Order ID field with rewritten result to [order_number]. And hide Order Number field.
Sorting is done on Order ID, but Order Number is shown.
Simple and fast solution :)

jpoesen’s picture

Having just been bitten by this, I must say: it's no fun.

I see the logic in the alphanumerical requirement, and I see the easy workaround.
But I also see the Order Number is actually called Order NUMBER so numerical behaviour is implied.

What's most problematic is that even though this 'works as designed', it's counter-intuitive and surprises the user so in my book that's quite a serious usability issue that *everyone* who uses simple numerical order numbers faces. Do you really want to force everyone in this situation to use a workaround?

bojanz’s picture

Status: Closed (works as designed) » Active

Okay, we obviously want to give this a bit more thought then.

googletorp’s picture

A simple solution would be to rename it from order number to order label.

What are you think on this matter Ryan?

googletorp’s picture

Version: 7.x-1.2 » 7.x-1.x-dev

Updating version number.

googletorp’s picture

Updating version number.

rszrama’s picture

Title: Sort method for orders » Consider an alternate sort method for orders or a different name for order numbers
Category: bug » task
Status: Active » Postponed (maintainer needs more info)

My driver's license number also has letters in it. More importantly, though, even straight numeric order numbers may have hyphens, requiring a varchar even if we only allowed numbers and hyphens.

With the available workaround, I'm not too concerned with changing this. We certainly can't change this in 1.x, and I'm not sure there's a clear advantage to changing this in the future. We can leave it open if folks want, but let's change its category. Maybe we just need to change the default View to use the approach in comment #3? If that's all, then we could make a change like that in 1.x.

I suppose another option would be to allow administrators to toggle the sort method in the order settings form.

jonathan_hunt’s picture

I just ran into this. I think it's a Store administrator WTF. @wzoom's work-around in #3 worked for me, thanks.

Every ecommerce store I've implemented to date has had numeric order ids so the ability to handle alpha ids is good to know but I'm not sure it should be the default option. I recommend making it a choice for the implementor, defaulting to numeric.

stewart.adam’s picture

I wanted to chime in and say that for me too #3 worked well, but that I also needed to set the new Order ID column to sortable & default in the table settings under "Format".

KarinG’s picture

The alphabetical ordering is confusing (almost a panic - as in is everything still working fine?) because order number #1000 ends up on page 15 (in our case) of admin/commerce/orders - but very pleased with how amazing Drupal Commerce is, this is very minor. Will update the view.

rszrama’s picture

Title: Consider an alternate sort method for orders or a different name for order numbers » Change the default Orders View's "Created" column to a "Last updated" column
Status: Postponed (maintainer needs more info) » Active
Issue tags: +1.5 blocker

I think we're going to go with this solution; basically it sidesteps the problem of order number sorting and fixes at the same time the problem we have with creation timestamps being somewhat useless as they're set at the first Add to Cart by default. My recommendation for people who don't alter Order Numbers at all would be to just use the Order ID in the View and sort on that if they want to reimplement that sort method.

cafuego’s picture

The usual fix for this in MySQL (to make legacy apps behave when the schema can't be changed) is to order by 'text field+0', which works fine if the field contains alphanumeric data, straight numeric data or both. Compare the following two queries:

mysql> select * from b order by c ASC;
+------+--------+
| i    | c      |
+------+--------+
|    9 | 1      |
|    5 | 1000   |
|   10 | 2      |
|    6 | 2000   |
|    8 | 3000   |
|   11 | 5      |
|    7 | 5000   |
|    4 | 5423vf |
|    8 | 6      |
|    1 | abc123 |
|    2 | abc321 |
|    3 | defwoo |
+------+--------+
12 rows in set (0.00 sec)

mysql> select * from b order by c+0 ASC;
+------+--------+
| i    | c      |
+------+--------+
|    1 | abc123 |
|    2 | abc321 |
|    3 | defwoo |
|    9 | 1      |
|   10 | 2      |
|   11 | 5      |
|    8 | 6      |
|    5 | 1000   |
|    6 | 2000   |
|    8 | 3000   |
|    7 | 5000   |
|    4 | 5423vf |
+------+--------+

I don't currently have access to a commerce install (let alone one with actual data), so I can't check if it would be possible to rewrite the query with a views hook. If so, a small extra module could fix this behaviour.

I don't know if this would work on other database systems or just break them.

kattekrab’s picture

FileSize
51.5 KB

This issue is really bugging me at the moment actually. Here's a screenshot.

screenshot of orders

I just don't see how the current alphabetical sort behaviour for order number is useful.

Looks like cafuego's database select option above WOULD be helpful here for the out of the box view. The work around in 3 might help a lot of people, but they have to know where to find it, and then implement it.

In my experience, most people USING this, would find a numeric sort for orders much more helpful, especially if they weren't able to get a developer to implement the work around above.

Any chance we could get this re-evaluated? Numeric sort for default seems to make sense.

cheers
Donna

cafuego’s picture

Have a look at https://drupal.org/sandbox/cafuego/1861714 for a teeny module that adds a new views sort handler for the order_number field.

googletorp’s picture

An important note on this.

Commerce (and Drupal) is getting big enough that we can't solve problems with MySQL hacks, since sites can be run on MySQL, PgSQL and soon probably also MSSQL Oracle etc.

Whatever solution we use would need to be database agnostic.

rszrama’s picture

Status: Active » Fixed

I went ahead and committed the fix I suggested in #13, so the admin/commerce/orders View now sorts by last updated (like the core Content view) and does not contain a created date. In Commerce, that created date is meaningless anyways, as an order can remain a cart for an untold amount of time before completing checkout. Sorting by created date or by order number thus results in orders that have just completed checkout appearing down the page unless some alternate strategy is used (like my site resetting the created date on checkout completion).

I almost changed the order number column to order ID so it would have proper numeric sorting, but joshmiller reminded me that we have a from in the header that jumps to a specific order by order number. I think it would be more confusing to have one use order number and the other use order ID than it would to change the View to essentially go against our established practice of using the order number for all representations of the order in the UI. It's a shame, honestly - this is an area where we desired a good level of flexibility but then made the default behavior cater to the edge case instead of the majority case.

I've removed the sortable option from the order number column for now, and I'd recommend that people in kattekrab's situation above simply replace the order number column with an order ID column.

dianikol’s picture

I override the order number to take a specific number when the order completed. Different than the order id. If i change the type from VARCHAR to INT in the database in order to have order by order number would it be a problem??

The change will be applied in the commerce_order and commerce_order_revision tables.

Strae’s picture

@dianikol changing the db directly is dangerous, even if in this case probably wont have side-effects.

I can suggest you two other ways (somewhat safer):

1. http://eureka.ykyuen.info/2012/06/16/drupal-7-sort-the-views-php-value/ (using views PHP)
2. Add an integer field to orders, then in a custom module via hook_entity_presave put in that field the order number, and use this field for sorting.

The 2 is probalby the best option imho, but views PHP doesnt require you to write a module (BUT use a potentially unsafe code!)

dianikol’s picture

Method 2 seems to be better, Anyway my guess is that changing the type in db wont cause me any errors. From what i've seen order number use is only for viewing the order.

Anyway I'll try the second method you menthioned.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

svouthi’s picture

Hello,

Sorry to reopen this, but it bugs me that much.

I second jpoesen in #4 above:
"What's most problematic is that even though this 'works as designed', it's counter-intuitive and surprises the user so in my book that's quite a serious usability issue that *everyone* who uses simple numerical order numbers faces. Do you really want to force everyone in this situation to use a workaround?"

I am getting ready to perform this workaround/alteration also. As a business person and novice drupaler/website builder who just wants to sell products online while meeting basic customer expectations, this seems like a rather unnecessary extra step for people like me who are looking for basic intuitive functionality out of the box. I appreciate the module, and I thank you for your continually awesome responsiveness, Ryan. However, this does seem silly.

Your ideas were great: "Maybe we just need to change the default View to use the approach in comment #3? If that's all, then we could make a change like that in 1.x. I suppose another option would be to allow administrators to toggle the sort method in the order settings form."

Although it may be technically true that this works as designed, not changing it leaves *everyone else* holding the bag.

rszrama’s picture

Hmm, have you used the most recent versions of Commerce? I did change the module so the default sort is by last changed, which is more meaningful regardless of the order ID. Because of the way we use the order entity as a shopping cart, sorting by order ID alone, even if it were always a true numerical sort, could still result in persons completing the checkout with old order IDs and being way down a list somewhere.

Please refer to my comment in #18 for more details.

svouthi’s picture

Hello, Ryan.

I am using Commerce 7.x-1.7, and I just reread your post at #18. The form in the header is surely useful for administrators to locate orders by number. I owe you an apology - not only because I'm not using the dev version of Commerce, but because I should have specified that I have been looking not at the admin orders list in this case (though this behaves in the same way), but at the user orders page - example.com/user/%/orders. I imagined that by clicking on Order Number the orders would sort numerically. I didn't expect the last changed to trump the numerical sort in such a case. It took me a moment to realize what was happening, so Joe Average Customer may wonder what on earth is going on also. If this isn't the place to discuss changing that default view, just say the word. Thanks again for your quick response.

rszrama’s picture

Hmm, you know, I may not have updated that View at the same time I updated the admin one. I'll give it another look! I think ultimately we'll just want to roll back how we're dealing with order numbers - i.e. make "alphanumeric" order numbers opt-in and simply change how Views integration to match the setting. In fact, that may be something we can accomplish in 1.x. I'll look into it, and if so, I'll open a separate issue for the feature.

svouthi’s picture

Thanks, Ryan. That would be fantastic.