Database error after update to 2.0.14c

[milstan]

Hi all,

After upgrade of my Joomla from 2.5.14 to 2.5.16 and upgrade of my VirtueMart from 2.0.14 to 2.0.14c, all category pages are blank. There are no products shown in the categories. I tried deleting template overrides for VM and using default layout files, but no luck. With debugging enabled, I get this error:

Code Select Expand

500 - JDatabaseMySQLi::query: 1054 - Unknown column 'p.product_name' in 'order clause' SQL=SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `ez7cr_virtuemart_products_en_gb` as l JOIN `ez7cr_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `ez7cr_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `ez7cr_virtuemart_categories_en_gb` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `ez7cr_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `ez7cr_virtuemart_product_shoppergroups`.`virtuemart_product_id` LEFT OUTER JOIN `ez7cr_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `ez7cr_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` WHERE ( p.`published`="1" AND `pc`.`virtuemart_category_id` = 14 AND `pc`.`virtuemart_category_id` > 0 AND ( s.`virtuemart_shoppergroup_id`= "1" OR s.`virtuemart_shoppergroup_id` IS NULL ) ) group by p.`virtuemart_product_id` ORDER BY p.product_name ASC LIMIT 0, 10

Please help.

Thanks

[GJC Web Design]

Hmmm..

the error is correct - there is no product_name in ez7cr_virtuemart_products (p)
it's in ez7cr_virtuemart_products_en_gb (l)

so the last bit of the query should be ORDER BY l.product_name ASC (if it was product name ordered)

try setting the default order of the products to something else in the VM config

if I have the products ordered by product name my query is

Code Select Expand

SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id`
  FROM `jos_virtuemart_products_en_gb` as l JOIN `jos_virtuemart_products` AS p using (`virtuemart_product_id`)
  LEFT JOIN `jos_virtuemart_product_categories` as pc
  ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id`
  LEFT JOIN `jos_virtuemart_categories_en_gb` as c
  ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id`
  LEFT JOIN `jos_virtuemart_product_shoppergroups`
  ON p.`virtuemart_product_id` = `jos_virtuemart_product_shoppergroups`.`virtuemart_product_id`
  LEFT
  OUTER JOIN `jos_virtuemart_shoppergroups` as s
  ON s.`virtuemart_shoppergroup_id` = `jos_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id`
  WHERE ( p.`published`="1"
  AND `pc`.`virtuemart_category_id` = 8
  AND `pc`.`virtuemart_category_id` > 0
  AND ( s.`virtuemart_shoppergroup_id`= "1" OR s.`virtuemart_shoppergroup_id` IS NULL ) )
  group by p.`virtuemart_product_id`
  ORDER BY product_name ASC
  LIMIT 0, 18

so the p. doesn't need to be there at all as product_name is unique.. all very odd

this query is built in administrator\components\com_virtuemart\models\product.php

another idea assuming it's coming from the filter_order - check your #__virtuemart_configs table -> configs field

what is your  browse_orderby_field=  set to?

e.g. mine is browse_orderby_field=s:12:"product_name";

[jenkinhill]

Most often reason for no products on pages with 2.0.24+ is to do with Joomla menu and category view selection.

This is a starter: http://forum.virtuemart.net/index.php?topic=120433.0

[Milbo]

Please go in your vm config and store the ordering settings again.