1. atrus
  2. RO CSVI
  3. Wednesday, 22 June 2016
  4.  Subscribe via email
Hi Roland,

When we try to export all categories of our VM3 installation (by choosing -Don't Use- in the category field), we get the following error:

Unknown column '#__virtuemart_categories.published' in 'where clause' SQL=SELECT `#__virtuemart_products`.`virtuemart_product_id`, `#__virtuemart_products`.`product_parent_id`, `product_sku`, `#__virtuemart_product_categories`.`virtuemart_category_id`, `#__virtuemart_currencies`.`currency_code_3`, `#__virtuemart_product_prices`.`virtuemart_shoppergroup_id`, `product_weight` FROM `#__virtuemart_products` LEFT JOIN `#__virtuemart_product_prices` ON `#__virtuemart_products`.`virtuemart_product_id` = `#__virtuemart_product_prices`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_product_manufacturers` ON `#__virtuemart_products`.`virtuemart_product_id` = `#__virtuemart_product_manufacturers`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_manufacturers` ON `#__virtuemart_product_manufacturers`.`virtuemart_manufacturer_id` = `#__virtuemart_manufacturers`.`virtuemart_manufacturer_id` LEFT JOIN `#__virtuemart_product_categories` ON `#__virtuemart_products`.`virtuemart_product_id` = `#__virtuemart_product_categories`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_currencies` ON `#__virtuemart_currencies`.`virtuemart_currency_id` = `#__virtuemart_product_prices`.`product_currency` LEFT JOIN `#__virtuemart_product_shoppergroups` ON `#__virtuemart_product_shoppergroups`.`virtuemart_product_id` = `#__virtuemart_products`.`virtuemart_product_id` WHERE `#__virtuemart_products`.`product_parent_id` = 0 AND `#__virtuemart_categories`.`published` = 1 AND #__virtuemart_products.published = 1 AND `#__virtuemart_product_prices`.`virtuemart_shoppergroup_id` = '3' GROUP BY `product_sku`

See attached the template, maybe it gives you a hint!

Rgrds,
Chris
Accepted Answer Pending Moderation
Hello Chris,

Forget my previous answer, it has to do with the Category State options. If you set that to Not Set, the export should run. I will work on a fix for that.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 1
Accepted Answer Pending Moderation
Thanks Roland, it is strange, just checked and the 'published' field is there.

Rgrds,
Chris
  1. more than a month ago
  2. RO CSVI
  3. # 2
Accepted Answer Pending Moderation
Hey Chris,

You are faster than expected, I changed my answer :) Can you please check my previous post.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 3
Accepted Answer Pending Moderation
Ok yes Roland, checked and you are right; managed to export all categories but without filtering the category state, noted abt the fix thanks!

Rgrds,
Chris
  1. more than a month ago
  2. RO CSVI
  3. # 4
Accepted Answer Pending Moderation
Hello Chris,

Attached is the patch file you can load, after that your export should work as expected.

Let me know how it goes.
Attachments (1)
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 5
Accepted Answer Pending Moderation
Thanks Roland, now getting the following error:

Error

Not unique table/alias: '#__virtuemart_product_categories' SQL=SELECT `#__virtuemart_products`.`virtuemart_product_id`, `#__virtuemart_products`.`product_parent_id`, `product_sku`, `#__virtuemart_product_categories`.`virtuemart_category_id`, `#__virtuemart_currencies`.`currency_code_3`, `#__virtuemart_product_prices`.`virtuemart_shoppergroup_id`, `product_weight` FROM `#__virtuemart_products` LEFT JOIN `#__virtuemart_product_prices` ON `#__virtuemart_products`.`virtuemart_product_id` = `#__virtuemart_product_prices`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_product_manufacturers` ON `#__virtuemart_products`.`virtuemart_product_id` = `#__virtuemart_product_manufacturers`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_manufacturers` ON `#__virtuemart_product_manufacturers`.`virtuemart_manufacturer_id` = `#__virtuemart_manufacturers`.`virtuemart_manufacturer_id` LEFT JOIN `#__virtuemart_product_categories` ON `#__virtuemart_products`.`virtuemart_product_id` = `#__virtuemart_product_categories`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_currencies` ON `#__virtuemart_currencies`.`virtuemart_currency_id` = `#__virtuemart_product_prices`.`product_currency` LEFT JOIN `#__virtuemart_product_shoppergroups` ON `#__virtuemart_product_shoppergroups`.`virtuemart_product_id` = `#__virtuemart_products`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_product_categories` ON `#__virtuemart_product_categories`.`virtuemart_product_id` = `#__virtuemart_products`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_categories` ON `#__virtuemart_categories`.`virtuemart_category_id` = `#__virtuemart_product_categories`.`virtuemart_category_id` WHERE `#__virtuemart_products`.`product_parent_id` = 0 AND `#__virtuemart_categories`.`published` = 1 AND #__virtuemart_products.published = 1 AND `#__virtuemart_product_prices`.`virtuemart_shoppergroup_id` = '4' GROUP BY `product_sku`

Rgrds,
Chris
  1. more than a month ago
  2. RO CSVI
  3. # 6
Accepted Answer Pending Moderation
Hey Chris,

I promise I tested the export before posting ;)

Attached an updated patch to deal with the last error. Let me know if this works for you.
Attachments (1)
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 7
Accepted Answer Pending Moderation
No worries, working ok now, thanks! ;)

Chris
  1. more than a month ago
  2. RO CSVI
  3. # 8
Accepted Answer Pending Moderation
Hello Roland!

Wishing the CSVI team all the best for the new year! keep up the great job!

Just updated to 7.0.0 and noted that the issue again comes up when trying to export all categories:

Unknown column '#__virtuemart_categories.published' in 'where clause' SQL=SELECT `#__virtuemart_products`.`virtuemart_product_id`, `#__virtuemart_products`.`product_parent_id`, `product_sku` FROM `#__virtuemart_products` LEFT JOIN `#__virtuemart_product_prices` ON `#__virtuemart_products`.`virtuemart_product_id` = `#__virtuemart_product_prices`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_product_manufacturers` ON `#__virtuemart_products`.`virtuemart_product_id` = `#__virtuemart_product_manufacturers`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_manufacturers` ON `#__virtuemart_product_manufacturers`.`virtuemart_manufacturer_id` = `#__virtuemart_manufacturers`.`virtuemart_manufacturer_id` LEFT JOIN `#__virtuemart_product_categories` ON `#__virtuemart_products`.`virtuemart_product_id` = `#__virtuemart_product_categories`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_currencies` ON `#__virtuemart_currencies`.`virtuemart_currency_id` = `#__virtuemart_product_prices`.`product_currency` LEFT JOIN `#__virtuemart_product_shoppergroups` ON `#__virtuemart_product_shoppergroups`.`virtuemart_product_id` = `#__virtuemart_products`.`virtuemart_product_id` WHERE `#__virtuemart_products`.`product_parent_id` = 0 AND `#__virtuemart_categories`.`published` = 1 AND #__virtuemart_products.published = 1 AND `#__virtuemart_product_prices`.`virtuemart_shoppergroup_id` = '4' GROUP BY #__virtuemart_products.virtuemart_product_id

Export works ok if i select 1 category.

Haven't been able to send a backup of the template (i get some error, will make a new post)

Rgrds,
Chris
  1. more than a month ago
  2. RO CSVI
  3. # 9
Accepted Answer Pending Moderation
Hello Chris,
Can you try to load the attached patch file and see if the error solves? If not we will solve your backup template issue first and check the issue with your template as i don't see this error with the template attached in this post initially.

patch_product_category_state_2016_01_09.zip
Attachments (1)
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 10
Accepted Answer Pending Moderation
Thanks Tharuna,

Just loaded the patch but issue remains. So i guess we need to try and backup the template first.

Rgrds,
Chris
  1. more than a month ago
  2. RO CSVI
  3. # 11
  • Page :
  • 1


There are no replies made for this post yet.
Be one of the first to reply to this post!