This error occurs with the 'Do not Display Product, if child products also out of stock' chosen and a VM search
500 error with Joomla debug on
No Search results when debug off
JDatabaseMySQL::query: 1052 - Column 'product_sku' in order clause is ambiguous
File administrator/components/com_virtuemart/models/product.php around line 245
(@version $Id: product.php 6255 2012-07-11 10:26:02Z Milbo $)(Version 2.0.8e)
When 'Do not Display Product, if child products also out of stock' is chosen in Configuration->ShopFront-> Action when a Product is Out of Stock an extra JOIN is made to the product and search query
LEFT OUTER JOIN `jos_virtuemart_products` children ON p.`virtuemart_product_id` = children.`product_parent_id`
This means there are 2 possible instances now of 'product_sku' but the WHERE clause is missing a TABLE ALIAS for 'product_sku' causing the 'Column 'product_sku' in where clause is ambiguous' error.
Original code line 245
$filter_search[] = '`' . $searchField . '` LIKE ' . $keyword;
Code Added line 245
if($searchField == 'product_sku') {
$filter_search[] = 'p.`' . $searchField . '` LIKE ' . $keyword;
}else{
$filter_search[] = '`' . $searchField . '` LIKE ' . $keyword;
}
A global addition of p. breaks other queries which is why the if statement
WHERE is now
WHERE ((`product_name` LIKE "%shirt%" OR p.`product_sku` LIKE "%shirt%" OR `product_s_desc` LIKE "%shirt%" OR `category_name` LIKE "%shirt%" OR `category_description` LIKE "%shirt%" OR `mf_name` LIKE "%shirt%")
I made another fix, using the function getValidProductFilterArray in shopfunctions, which is meant for it. So it should also work with
$filterArray = array('product_name', 'p.created_on', 'p.product_sku', .........
You may investigate more similar problems, due your heavy use of parents. It should be enough to add a p. there. Of course we are happy, when you share your knowledge with us :-)