Query problem when Do not Display Product, if child products also out of stock’

[GJC Web Design]

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%")

[Milbo]

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 :-)