Ordering products in orders by SKU in backend

[mhilliard]

We've been struggling to override the default listing of products in each order so they listed by SKU and not the sequence they were added.

I think it has something to with this file (models/orders.php) but could be wrong. Our client pulls all their inventory by SKU number in order and it would make processing/shipping a lot easier for them. Does anyone have insight to what line of code needs to change or is aware of a component that will make the columns sortable by heading on the backend?

We're working on VM 3.2.4 and Joomla 3.8.0

[AH]

You could adjust the model to do this:-

in the order.php


<<modified to remove bold tags in code snippet>>

Code Select Expand


// Get the order items
$q = 'SELECT virtuemart_order_item_id, product_quantity, order_item_name, order_item_sku, i.virtuemart_product_id, product_item_price, product_final_price, product_basePriceWithTax, product_discountedPriceWithoutTax, product_priceWithoutTax, product_subtotal_with_tax, product_subtotal_discount, product_tax, product_attribute, order_status,
intnotes, virtuemart_category_id
FROM #__virtuemart_order_items i
LEFT JOIN #__virtuemart_products p
ON p.virtuemart_product_id = i.virtuemart_product_id
LEFT JOIN #__virtuemart_product_categories c
ON p.virtuemart_product_id = c.virtuemart_product_id
WHERE `virtuemart_order_id`="'.$virtuemart_order_id.'" group by `virtuemart_order_item_id`
ORDER BY order_item_sku ASC';




Tested on vm 3.2.4
However you will need to reapply this every time you update VM

It does not allow selection of columns though.

[mhilliard]

That works! However the last line ( ORDER BY order_item_sku ASC'; ) threw an error with the ... tags. Pulling those out had everything ordered by SKU on the backend. Thanks.

Here's the code that did the job:

Code Select Expand


// Get the order items
$q = 'SELECT virtuemart_order_item_id, product_quantity, order_item_name, order_item_sku, i.virtuemart_product_id, product_item_price, product_final_price, product_basePriceWithTax, product_discountedPriceWithoutTax, product_priceWithoutTax, product_subtotal_with_tax, product_subtotal_discount, product_tax, product_attribute, order_status,
intnotes, virtuemart_category_id
FROM #__virtuemart_order_items i
LEFT JOIN #__virtuemart_products p
ON p.virtuemart_product_id = i.virtuemart_product_id
LEFT JOIN #__virtuemart_product_categories c
ON p.virtuemart_product_id = c.virtuemart_product_id
WHERE `virtuemart_order_id`="'.$virtuemart_order_id.'" group by `virtuemart_order_item_id`

ORDER BY order_item_sku ASC';



[AH]

Drat - the tags were designed to highlight the modification you should make

[mhilliard]

Well, this is weird. It worked properly a month ago, but we've since migrated the site and reinstalled VM (currently running vm3.2.4 and Joomla 3.8.1) during the process, and now this patch won't work.

It's possible we applied the patch to a vm3.2.2 version of the site earlier... can you think of any reason that would change in vm3.2.4 for administrator/components/com_virtuemart/models/orders.php?

[AH]

The patch should still work but you could try adding the `s

Code Select Expand



// Get the order items
$q = 'SELECT virtuemart_order_item_id, product_quantity, order_item_name, order_item_sku, i.virtuemart_product_id, product_item_price, product_final_price, product_basePriceWithTax, product_discountedPriceWithoutTax, product_priceWithoutTax, product_subtotal_with_tax, product_subtotal_discount, product_tax, product_attribute, order_status,
intnotes, virtuemart_category_id
FROM #__virtuemart_order_items i
LEFT JOIN #__virtuemart_products p
ON p.virtuemart_product_id = i.virtuemart_product_id
LEFT JOIN #__virtuemart_product_categories c
ON p.virtuemart_product_id = c.virtuemart_product_id
WHERE `virtuemart_order_id`="'.$virtuemart_order_id.'" group by `virtuemart_order_item_id`
ORDER BY `order_item_sku` ASC';



[compaid]

cant get this to work on VM 3.6.10, joomla 3.9.20

Just stays on same order as added to cart.

Are there any switches that need to be put on. Tried adding product order settings for product name and sku but no change or separate. Would really like this as the picking order is by SKU in the warehouse.

[AH]

There is a new"ish"  hidden config

"order_item_ordering=order_item_sku"

look for this in virtuemart.cfg

remove any # (comments the line out)

Then go to vm configuration in BE and save