3

Maybe I'm just stupid and can't see the obvious thing but ..

There's are 3 different calculation methods available with table rates in Magento and one of them is "# of Items vs. Destination". So I can hope the idea of this method is to specify different rates depending on the number of items in the cart.

Let's take a example.

Let's assume that we want the shipping rate to be X if number of items in cart is below 60 and Y for 60 items and above.

For this purpose we compile the following CSV:

Country,Region/State,"Zip/Postal Code","# of Items (and above)","Shipping Price"
*,      *,           *,                1.0000,                  X
*,      *,           *,                60.0000,                 Y

After quick debugging of Mage_Shipping_Model_Resource_Carrier_Tablerate::getRate() I found out that it will result in the following query:

  SELECT `shipping_tablerate`.* 
    FROM `shipping_tablerate` 
   WHERE (website_id = :website_id) 
     AND ((dest_country_id = :country_id 
           AND dest_region_id = :region_id 
           AND dest_zip = :postcode) 
         OR (dest_country_id = :country_id 
          AND dest_region_id = :region_id 
          AND dest_zip = '') 
         OR (dest_country_id = :country_id 
          AND dest_region_id = :region_id 
          AND dest_zip = '*') 
         OR (dest_country_id = :country_id 
          AND dest_region_id = 0 
          AND dest_zip = '*') 
         OR (dest_country_id = '0' 
          AND dest_region_id = :region_id 
          AND dest_zip = '*') 
         OR (dest_country_id = '0' 
          AND dest_region_id = 0 
          AND dest_zip = '*') 
         OR (dest_country_id = :country_id 
          AND dest_region_id = 0 
          AND dest_zip = '') 
         OR (dest_country_id = :country_id 
          AND dest_region_id = 0 
          AND dest_zip = :postcode) 
         OR (dest_country_id = :country_id 
          AND dest_region_id = 0 
          AND dest_zip = '*')) 
     AND (condition_name = :condition_name) 
     AND (condition_value <= :condition_value) 
ORDER BY `dest_country_id` DESC, `dest_region_id` DESC, `dest_zip` DESC    
   LIMIT 1

Don't be confused with all those conditions. The only one that matters is this one:

     AND (condition_value <= :condition_value)

This means that if we have let's say 63 items in the cart it will satisfy both lines of our CSV because 1 <= 63 as well as 60 <= 63. This means all will come down to ORDER BY which as you can see not making any sense. As a result we will be always getting the first line and X rate.

So I will finish with the same pattern as I startet. Am I missing something obvious or is there something horribly wrong?

  • 2
    From my point of view, the condition is too limited and you will get for sure a problem depending on the ORDER BY which unfortunately doesn't sort by condition_value. This part is missing in the ORDER BY. So yes, I agree with you, the SQL query is not enough precise and can provide confusion. – Sylvain Rayé Apr 5 '13 at 19:36
  • Maybe it is sorted while inserting? so the return value is the right one? Crazy idea, I know. Did you try it? And did you find wrong return values? – Fabian Blechschmidt Apr 6 '13 at 4:38
  • No it's not, I extended the table rates a few times and the items are inserted with insertArray, so in the table they will appear in the same order they are found in the CSV. Good catch Tim, didn't know about this. A pull request might be a good thing! – fmrng Apr 6 '13 at 7:45
  • Guys, why don't you put these all as an answers? – Foo Bar Apr 6 '13 at 9:07
  • Because it is not helpful :D Did you have a look into the SQL of the other options? It looks like, these options was never used before? :-) – Fabian Blechschmidt Apr 6 '13 at 14:57
4

It's almost certainly a bug, since Magento is not sorting the condition values in any way. Rates are not sorted during import either (as you can see, entries are inserted in the same order they are found in the CSV using insertArray), so Magento is still able to load the correct rate if the order in the import is correct. This query you submitted can lead to wrong results though, without one noticing.

To fix this, it would be enough to edit Mage_Shipping_Model_Resource_Carrier_Tablerate, line 128 (CE 1.7.0.2) with:

->order(array('dest_country_id DESC', 'dest_region_id DESC', 'dest_zip DESC', 'condition_value DESC'))

A pull request would be the right thing to do I guess.

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.