Author Topic: Query problem when Do not Display Product, if child products also out of stock’  (Read 2574 times)

GJC Web Design

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 10688
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 3.8.8
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%")
GJC Web Design
VirtueMart and Joomla Developers - php developers https://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 10530
  • VM4.0.7 Eagle Owl
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
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 :-)
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/