J!Extensions Store™
Forum
Welcome, Guest
Please Login to access forum.
[SOLVED]Automatically exclude products from unpublished categories in Virtuemart data source. (1 viewing) 
Go to bottom
TOPIC: [SOLVED]Automatically exclude products from unpublished categories in Virtuemart data source.
#7740
Sonja Kueppers
Fresh Boarder
Posts: 2
User Offline
[SOLVED]Automatically exclude products from unpublished categories in Virtuemart data source. Karma: 0  
I'd like to automatically exclude products from unpublished categories in the Virtuemart Products data source.

I've succeeded in causing the products not to appear by excluding their specific category ID's under Join Table 2, but I think I'd need to be able to join a fourth table, the virtuemart_categories table, in order to exclude items from unpublished categories automatically, and I can't see a way to do that.

Is there a way to specify my own SQL? Or is there something I am missing?

Thank you for your time!
 
Logged Logged  
  The topic has been closed.
#7742
John Dagelmore
Admin
Posts: 3716
User Online Now
Re:Automatically exclude products from unpublished categories in Virtuemart Products data source. Karma: 79  
Hi,

to do this you have to add a custom JOIN to the raw SQL string.

It must be as below for example:

Code:

SELECT `#__virtuemart_products_en_gb`.`product_name`, `#__virtuemart_products_en_gb`.`virtuemart_product_id`, `#__virtuemart_product_categories`.`virtuemart_category_id`, `#__virtuemart_categories_en_gb`.`category_name` FROM `#__virtuemart_products_en_gb` JOIN `#__virtuemart_products` ON `#__virtuemart_products_en_gb`.`virtuemart_product_id` = `#__virtuemart_products`.`virtuemart_product_id` JOIN `#__virtuemart_product_categories` ON `#__virtuemart_products`.`virtuemart_product_id` = `#__virtuemart_product_categories`.`virtuemart_product_id` JOIN `#__virtuemart_categories_en_gb` ON `#__virtuemart_product_categories`.`virtuemart_category_id` = `#__virtuemart_categories_en_gb`.`virtuemart_category_id` JOIN `#__virtuemart_categories` ON `#__virtuemart_categories`.`virtuemart_category_id` = `#__virtuemart_product_categories`.`virtuemart_category_id` WHERE `#__virtuemart_products`.`published` = '1' AND `#__virtuemart_categories`.`published` = '1' ORDER BY `#__virtuemart_categories_en_gb`.`category_name` ASC, `#__virtuemart_products`.`pordering` ASC
where there is one more JOIN:
Code:

JOIN `#__virtuemart_categories` ON `#__virtuemart_categories`.`virtuemart_category_id` = `#__virtuemart_product_categories`.`virtuemart_category_id`
and one more WHERE clause:
Code:

AND `#__virtuemart_categories`.`published` = '1'
Best regards!
 
Logged Logged  
  The topic has been closed.
#7743
Sonja Kueppers
Fresh Boarder
Posts: 2
User Offline
Re:Automatically exclude products from unpublished categories in Virtuemart Products data source. Karma: 0  
Thank you, that did it. I had missed that I was allowed to edit the auto-generated SQL!

I appreciate your quick response.
 
Logged Logged  
  The topic has been closed.
Go to top