Take a look at http://goo.gl/N2ZzzZ
If I do a sort by price, ASC or DESC, the product sorting is all over the place (just compare the $ values).
Probably because some of the products are single products and some are configurable with multiple products defined within them.
I think Magento takes the highest priced product in those configurable products and then uses that for the sort, but if I order the products from min to max then that is all off.
Any idea if this can be fixed somehow?
I cannot give you a clear answer, but I can give you a place to start.
The product listing uses the prices from the price index table catalog_product_index_price.
When you sort by price, the column min_price is used for sorting.
You can see that in Mage_Catalog_Model_Resource_Product_Collection::addAttributeToSort in the if($attribute == 'price') section:
$this->addPriceData();
$this->getSelect()->order("price_index.min_price {$dir}");
Now the crazy part.
The price index table is populated by an indexer.
Each product type price is calculated a bit differently.
The classes that compute the prices are located in app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price. each class corresponds to a product type. If there is no class for a specific product type (like virtual for example) Default.php is used.
For bundle products the class used is app/code/core/Mage/Bundle/Model/Resource/Indexer/Price.php and for downloadable app/code/core/Mage/Downloadable/Model/Resource/Indexer/Price.php
Each class has a method called reindexEntity. That's the one that does the dirty job.
That method calls different methods depending on the product type, that calculate and retrieves the prices.
In each class (specially Configurable, Grouped and Bundle) there are some huge queries to get the prices.
Unfortunately I cannot really explain what each query does because they look like dragons to me at a first glance.
But you can run indexer processes and log the queries and the results if you want to see what they do.
Also if you want to change the indexing process these are the classes and methods you need to change. But be very careful.
