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:
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
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
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";
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
Please go in your vm config and store the ordering settings again.