If you use the 'display to customers' flag in management center to unpublish select SKUs, you may have noticed that the facet count on the storefront is still tallying these unpublished items. For example:
1. Create a product: 'Shirt'
2. Assign it an attribute 'Colour' (and make this attribute facetable)
3. Create a couple of items for the product, with attribute values 'Red', 'Blue', 'Green'.
4. For the shirt SKU of color 'Blue', uncheck 'display to customers'
5. Run Preprocess/buildindex
6. On the storefront, search for 'shirt'. On the left nav, you'll see all size attributes appear, including 'Blue', which only belongs to the unpublished SKU
7. Click on the 'blue' facet. 0 products/SKUs are returned.
This can be frustrating for shoppers, who think that a blue shirt is available, but then can't actually display the product.
You can change this behaviour by updating a di-preprocess XML. Locate the file wc-dataimport-preprocess-attribute.xml for the CatalogEntry index you want to change, for example:
<CommerceServer70 install dir>/instances/<instance name>/search/pre-processConfig/MC_10001/DB2/wc-dataimport-preprocess-attribute.xml
Make the following change in red to the SQL section:
(select catentryattr.catentry_id , attrvaldesc.attr_id attr_id, attrvaldesc.stringvalue attr_stringvalue, attrvaldesc.integervalue attr_integervalue, attrvaldesc.floatvalue attr_floatvalue, af.attrtype_id attrtype_id, af.srchfieldname attr_name from catentryattr,attrvaldesc, TI_CATENTRY_4 CE, attrdictsrchconf af
where catentryattr.catentry_id=CE.catentry_id and catentryattr.attr_id in (?attr_id?) and attrvaldesc.attrval_id=catentryattr.attrval_id and attrvaldesc.language_id=?language_id? and af.attr_id=attrvaldesc.attr_id)
UNION
(select c.catentry_id_parent CATENTRY_ID, attrvaldesc.attr_id attr_id, attrvaldesc.stringvalue attr_stringvalue, attrvaldesc.integervalue attr_integervalue, attrvaldesc.floatvalue attr_floatvalue, af.attrtype_id attrtype_id, af.srchfieldname attr_name
from catentryattr,attrvaldesc, TI_CATENTREL_4 c, attrdictsrchconf af, catentdesc cd
where c.catentry_id_child=catentryattr.catentry_id and cd.catentry_id = c.catentry_id_child and cd.language_id = ?language_id? and cd.published = 1 and
attrvaldesc.attrval_id=catentryattr.attrval_id and attrvaldesc.language_id=?language_id? and
af.attr_id=attrvaldesc.attr_id and
catentryattr.attr_id in (?attr_id?))
order by catentry_id, attrtype_id
Then run preprocess and buildindex