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!

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!

Thank you, that did it. I had missed that I was allowed to edit the auto-generated SQL!

I appreciate your quick response.