J!Extensions Store™
Forum
Welcome, Guest
Please Login to access forum.
Re:Automatically exclude products from unpublished categories in Virtuemart Products data source. (1 viewing) 
Go to bottom
TOPIC: Re:Automatically exclude products from unpublished categories in Virtuemart Products data source.
#7740
Sonja Kueppers
Fresh Boarder
Posts: 2
User Offline
[SOLVED]Automatically exclude products from unpublished categories in Virtuemart data source. 2 Years ago 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  
 
Last Edit: 2017/09/15 18:35 By @store$eco#mm!.
  The administrator has disabled public write access.
#7742
John Dagelmore
Admin
Posts: 3516
User Offline
Re:Automatically exclude products from unpublished categories in Virtuemart Products data source. 2 Years ago Karma: 75  
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  
 
Last Edit: 2017/09/15 09:03 By @store$eco#mm!.
  The administrator has disabled public write access.
#7743
Sonja Kueppers
Fresh Boarder
Posts: 2
User Offline
Re:Automatically exclude products from unpublished categories in Virtuemart Products data source. 2 Years ago 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 administrator has disabled public write access.
Go to top