clause is ambiguous SQL error

[alanbagl;ey]

Hi,

Using Joomla! 2.5.14 and VM 2.0.10

I have been following these threads: http://forum.virtuemart.net/index.php?topic=101053.0 and http://forum.virtuemart.net/index.php?topic=111437.msg374409#msg374409 but I still have an error when trying to use the VM search.

With Debug on and using the VM search module I get the following error:

500 - JDatabaseMySQL::query: 1052 - Column 'ordering' in where clause is ambiguous SQL=SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `jQWs_virtuemart_products_en_gb` as l JOIN `jQWs_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `jQWs_virtuemart_product_categories` ON p.`virtuemart_product_id` = `jQWs_virtuemart_product_categories`.`virtuemart_product_id` LEFT JOIN `jQWs_virtuemart_categories_en_gb` as c ON c.`virtuemart_category_id` = `jQWs_virtuemart_product_categories`.`virtuemart_category_id` LEFT JOIN `jQWs_virtuemart_product_manufacturers` ON p.`virtuemart_product_id` = `jQWs_virtuemart_product_manufacturers`.`virtuemart_product_id` LEFT JOIN `jQWs_virtuemart_manufacturers_en_gb` as m ON m.`virtuemart_manufacturer_id` = `jQWs_virtuemart_product_manufacturers`.`virtuemart_manufacturer_id` LEFT JOIN `jQWs_virtuemart_product_prices` as pp ON p.`virtuemart_product_id` = pp.`virtuemart_product_id` LEFT JOIN `jQWs_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `jQWs_virtuemart_product_shoppergroups`.`virtuemart_product_id` LEFT OUTER JOIN `jQWs_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `jQWs_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` WHERE ((`product_name` LIKE "%car paint%" OR `p`.product_sku LIKE "%car paint%" OR `product_s_desc` LIKE "%car paint%" OR `product_desc` LIKE "%car paint%" OR `category_name` LIKE "%car paint%" OR `category_description` LIKE "%car paint%" OR `mf_name` LIKE "%car paint%" OR `product_price` LIKE "%car paint%" OR `product_special` LIKE "%car paint%" OR `product_availability` LIKE "%car paint%" OR `product_available_date` LIKE "%car paint%" OR `product_height` LIKE "%car paint%" OR `product_width` LIKE "%car paint%" OR `product_length` LIKE "%car paint%" OR `product_weight` LIKE "%car paint%" OR `product_weight_uom` LIKE "%car paint%" OR `product_in_stock` LIKE "%car paint%" OR `product_unit` LIKE "%car paint%" OR `p`.virtuemart_product_id LIKE "%car paint%" OR `ordering` LIKE "%car paint%") AND p.`published`="1" AND ( s.`virtuemart_shoppergroup_id`= "1" OR s.`virtuemart_shoppergroup_id` IS NULL ) ) group by p.`virtuemart_product_id` ORDER BY `p`.created_on ASC LIMIT 0, 10

This is the call stack:
Call stack
#   Function   Location
1   JSite->dispatch()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/index.php:42
2   JComponentHelper::renderComponent()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/includes/application.php:197
3   JComponentHelper::executeComponent()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/libraries/joomla/application/component/helper.php:351
4   require_once()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/libraries/joomla/application/component/helper.php:383
5   JController->execute()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/components/com_virtuemart/virtuemart.php:104
6   VirtueMartControllerCategory->display()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/libraries/joomla/application/component/controller.php:761
7   JController->display()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/components/com_virtuemart/controllers/category.php:60
8   VirtuemartViewCategory->display()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/libraries/joomla/application/component/controller.php:722
9   VirtueMartModelProduct->getProductsInCategory()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/components/com_virtuemart/views/category/view.html.php:170
10   VirtueMartModelProduct->sortSearchListQuery()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/administrator/components/com_virtuemart/models/product.php:1038
11   VmModel->exeSortSearchListQuery()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/administrator/components/com_virtuemart/models/product.php:514
12   JDatabase->loadResultArray()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/administrator/components/com_virtuemart/helpers/vmmodel.php:365
13   JDatabase->loadColumn()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/libraries/joomla/database/database.php:1842
14   JDatabaseMySQL->execute()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/libraries/joomla/database/database.php:965
15   JError::raiseError()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/libraries/joomla/database/database/mysql.php:548
16   JError::raise()   /var/www/vhosts/carcolourservices.co.uk/httpdocs/libraries/joomla/error/error.php:251

Any advise would be great

Kind regards
Alan

[Milbo]

The advise is to update, current version is atm 2.0.24

[alanbagl;ey]

Hi Milbo,

is there a plan 'B'? There has been a lot of customization to the site and I am hoping to find a solution that does not mean I have to update to current version 2.0.24.

Does the error message above show that there is an issue with the Column 'ordering' ? Because I have also noticed that I can not "re-order" my products in the admin area as I usually could in VM 1

If there is a "hack" solution to this and it can be provided or a work around that would be great?

Kind regards
Alan

[Milbo]

It is also enough maybe, just to disable the ordering.

But you should try an update on another host. We just had someone updating successfully without big hazzles from vm1.9.8 to vm2.0.24. So you definitly should try it. vm2.0.10 has also some security issues.

[alanbagl;ey]

Thanks Milbo,

How would I go about disabling the ordering?

With regards to update, I will have to get this done but because this is a production site I will have to, move it to a dev server then update, test then move back, hence looking for a work around while doing this

Kind regards
Alan

[Milbo]

It is now in the vm config, dont know how it was in vm2.0.10, sry