2

I have a confusing issue regarding assignment of products to categories in the re-indexing process (population of catalog_category_product_index). Basically, it seems to assign products to the parent category and not the specific subcategory.

I have checked this by adding a new root category and a new sub category, and the product is only assigned to the root/parent category in the index table. I've traced this to Magento\Catalog\Model\Indexer\Category\Product\AbstractAction with the following SQL:

protected function createAnchorSelect(\Magento\Store\Model\Store $store)
{
    $isAnchorAttributeId = $this->config->getAttribute(
        \Magento\Catalog\Model\Category::ENTITY,
        'is_anchor'
    )->getId();
    $statusAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'status')->getId();
    $visibilityAttributeId = $this->config->getAttribute(
        \Magento\Catalog\Model\Product::ENTITY,
        'visibility'
    )->getId();
    $rootCatIds = explode('/', $this->getPathFromCategoryId($store->getRootCategoryId()));
    array_pop($rootCatIds);

    $temporaryTreeTable = $this->makeTempCategoryTreeIndex();

    $productMetadata = $this->getMetadataPool()->getMetadata(\Magento\Catalog\Api\Data\ProductInterface::class);
    $categoryMetadata = $this->getMetadataPool()->getMetadata(\Magento\Catalog\Api\Data\CategoryInterface::class);
    $productLinkField = $productMetadata->getLinkField();
    $categoryLinkField = $categoryMetadata->getLinkField();

    return $this->connection->select()->from(
        ['cc' => $this->getTable('catalog_category_entity')],
        []
    )->joinInner(
        ['cc2' => $temporaryTreeTable],
        'cc2.parent_id = cc.entity_id AND cc.entity_id NOT IN (' . implode(
            ',',
            $rootCatIds
        ) . ')',
        []
    )->joinInner(
        ['ccp' => $this->getTable('catalog_category_product')],
        'ccp.category_id = cc2.child_id',
        []
    )->joinInner(
        ['cpe' => $this->getTable('catalog_product_entity')],
        'ccp.product_id = cpe.entity_id',
        []
    )->joinInner(
        ['cpw' => $this->getTable('catalog_product_website')],
        'cpw.product_id = ccp.product_id',
        []
    )->joinInner(
        ['cpsd' => $this->getTable('catalog_product_entity_int')],
        'cpsd.' . $productLinkField . ' = cpe.' . $productLinkField . ' AND cpsd.store_id = 0'
            . ' AND cpsd.attribute_id = ' . $statusAttributeId,
        []
    )->joinLeft(
        ['cpss' => $this->getTable('catalog_product_entity_int')],
        'cpss.' . $productLinkField . ' = cpe.' . $productLinkField . ' AND cpss.attribute_id = cpsd.attribute_id' .
        ' AND cpss.store_id = ' .
        $store->getId(),
        []
    )->joinInner(
        ['cpvd' => $this->getTable('catalog_product_entity_int')],
        'cpvd.' . $productLinkField . ' = cpe. ' . $productLinkField . ' AND cpvd.store_id = 0' .
        ' AND cpvd.attribute_id = ' .
        $visibilityAttributeId,
        []
    )->joinLeft(
        ['cpvs' => $this->getTable('catalog_product_entity_int')],
        'cpvs.' . $productLinkField . ' = cpe.' . $productLinkField .
        ' AND cpvs.attribute_id = cpvd.attribute_id ' . 'AND cpvs.store_id = ' .
        $store->getId(),
        []
    )->joinInner(
        ['ccad' => $this->getTable('catalog_category_entity_int')],
        'ccad.' . $categoryLinkField . ' = cc.' . $categoryLinkField . ' AND ccad.store_id = 0' .
        ' AND ccad.attribute_id = ' . $isAnchorAttributeId,
        []
    )->joinLeft(
        ['ccas' => $this->getTable('catalog_category_entity_int')],
        'ccas.' . $categoryLinkField . ' = cc.' . $categoryLinkField
        . ' AND ccas.attribute_id = ccad.attribute_id AND ccas.store_id = ' .
        $store->getId(),
        []
    )->where(
        'cpw.website_id = ?',
        $store->getWebsiteId()
    )->where(
        $this->connection->getIfNullSql('cpss.value', 'cpsd.value') . ' = ?',
        \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED
    )->where(
        $this->connection->getIfNullSql('cpvs.value', 'cpvd.value') . ' IN (?)',
        [
            \Magento\Catalog\Model\Product\Visibility::VISIBILITY_IN_CATALOG,
            \Magento\Catalog\Model\Product\Visibility::VISIBILITY_IN_SEARCH,
            \Magento\Catalog\Model\Product\Visibility::VISIBILITY_BOTH
        ]
    )->where(
        $this->connection->getIfNullSql('ccas.value', 'ccad.value') . ' = ?',
        1
    )->columns(
        [
            'category_id' => 'cc.entity_id',
            'product_id' => 'ccp.product_id',
            'position' => new \Zend_Db_Expr('ccp.position + 10000'),
            'is_parent' => new \Zend_Db_Expr('0'),
            'store_id' => new \Zend_Db_Expr($store->getId()),
            'visibility' => new \Zend_Db_Expr($this->connection->getIfNullSql('cpvs.value', 'cpvd.value')),
        ]
    );
}

Running the SELECT this produces directly against the DB I can 'fix' the data by changing the 'category_id' => 'cc.entity_id' column to instead be 'category_id' => 'cc2.child_id', but then I run into all sorts of issues (in the code) that mean I can't simply drop this fix in. This is a major issue with Magento 2, as best I can tell, what can I do to resolve it?

For reference, the tables are as follows after an index:

catalog_category_entity

{
    "entity_id": 1056,
    "attribute_set_id": 3,
    "parent_id": 1,
    "created_at": "2017-05-19 18:20:18",
    "updated_at": "2017-05-19 18:23:10",
    "path": "1/1056",
    "position": 2,
    "level": 1,
    "children_count": 1
},
{
    "entity_id": 1057,
    "attribute_set_id": 3,
    "parent_id": 1056,
    "created_at": "2017-05-19 18:23:10",
    "updated_at": "2017-05-19 18:23:10",
    "path": "1/1056/1057",
    "position": 1,
    "level": 2,
    "children_count": 0
}

catalog_category_product

{
    "entity_id": 56647,
    "category_id": 1057,
    "product_id": 7761,
    "position": 0
}

catalog_category_product_index

{
    "category_id": 1056,
    "product_id": 7761,
    "position": 10000,
    "is_parent": 0,
    "store_id": 1,
    "visibility": 4
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.