magento2: Products added to a category are not shown at the frontend.

Preconditions (*)

1. Magento CE <= 2.2.6
2. Magento Commerce Edition <= 2.2.6

Steps to reproduce (*)

1. Run magento indexer:reindex to make all indexes up to date.
2. Switch all indexes to the “Update Scheduled” mode.
3. Set up Cron.
4. Create a simple product (visible at the frontend, qty > 0)
5. Create a category.
6. Go to the frontend and make sure that the category is empty.
7. Open the created category (step 4) and assign the created product to it.
8. Save category changes.
9. Wait until Cron reindexes category changes or run magento indexer:reindex catalog_category_product.
10. Open the category at the frontend.

Expected result (*)

1. Category must contain the product.

Actual result (*)

1. The category is still empty

The problem

It looks like the category uses the information from the ‘catalogsearch_fulltext’ index because running magento indexer:reindex catalogsearch_fulltext solves the issue.

The solution

The solution is simple: track changes in the “catalog_category_product” table and update the “catalogsearch_fulltext_cl” changelog table.

This can be done by adding an additional subscriber: <table name="catalog_category_product" entity_column="product_id" /> to the vendor/magento/module-catalog-search/etc/mview.xml file.

So the updated file should look this way:

<?xml version="1.0" encoding="UTF-8"?>
<!--
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Mview/etc/mview.xsd">
    <view id="catalogsearch_fulltext" class="\Magento\CatalogSearch\Model\Indexer\Mview\Action" group="indexer">
        <subscriptions>
            <table name="catalog_product_entity" entity_column="entity_id" />
            <table name="catalog_product_entity_int" entity_column="entity_id" />
            <table name="catalog_product_entity_decimal" entity_column="entity_id" />
            <table name="catalog_product_entity_text" entity_column="entity_id" />
            <table name="catalog_product_entity_varchar" entity_column="entity_id" />
            <table name="catalog_product_entity_datetime" entity_column="entity_id" />
            <table name="catalog_product_website" entity_column="product_id" />
            <table name="cataloginventory_stock_item" entity_column="product_id" />
            <table name="catalog_product_bundle_selection" entity_column="parent_product_id" />
            <table name="catalog_product_super_link" entity_column="product_id" />
            <table name="catalog_product_link" entity_column="product_id" />
            <table name="catalog_category_product" entity_column="product_id" />
        </subscriptions>
    </view>
</config>