We have a custom field on our products on a clothing, where site admin is required to select xs,s, m, l or xl.
xs = size 8, s = 10, m = 12, l = 14 and xl = 16+.
We then have a page for each of 8, 10, 12, 14 and 16+.
We then filter each of these pages using the following code (developed by Aleksey, thanks!):
<?php $size = 'l'; $products = Shop_Product::create()->apply_filters()->order('created_at desc'); $products->apply_visibility()->apply_customer_group_visibility(); $products->where(' ((shop_products.enabled=1 and (disable_completely is null or disable_completely=0) and not ( shop_products.track_inventory is not null and shop_products.track_inventory=1 and shop_products.hide_if_out_of_stock is not null and shop_products.hide_if_out_of_stock=1 and ((shop_products.stock_alert_threshold is not null and shop_products.in_stock <= shop_products.stock_alert_threshold) or (shop_products.stock_alert_threshold is null and shop_products.in_stock=0)) )) or exists(select * from shop_products grouped_products where grouped_products.product_id is not null and grouped_products.product_id=shop_products.id and grouped_products.enabled=1 and not ( grouped_products.track_inventory is not null and grouped_products.track_inventory=1 and grouped_products.hide_if_out_of_stock is not null and grouped_products.hide_if_out_of_stock=1 and ((grouped_products.stock_alert_threshold is not null and grouped_products.in_stock <= grouped_products.stock_alert_threshold) or (grouped_products.stock_alert_threshold is null and grouped_products.in_stock=0)) )))'); $products->where(' ((disable_completely is null or disable_completely=0) and product_id is null) or ( product_id is not null and (select ifnull(parent.disable_completely, 0) from shop_products parent where parent.id=shop_products.product_id) = 0 ) '); $products->where('x_product_size=?', $size); $this->render_partial('shop:product_list', array( 'products'=>$products, 'paginate'=>false, 'page_index'=>$this->request_param(1, 0), )) ?>
The issue we are having is that if ->apply_filters() is used, then the output does not display products where the master product has a stock level of 0. If we remove ->apply_filters(), then it shows all products for that size, regardless of stock level, as long as the master product has stock. If we use ->apply_filters() on size xl, then no products show at all.
Thanks for your help.