Magento 2 displays configurable products on the PLP page whose SKUs correspond to the custom varchar attribute value of simple products

Magento 2: We want to retrieve only the configurable products whose SKU value matches the simple products’ custom varchar value and whose quantity is more than 0 on the PLP page.

Because of the variations in size and color, we have linked simple products to more configurable ones in our Magento store. We now like to display on the PLP page only those configurable items whose SKU value corresponds to the simple products and whose quantity value is more than 0.

All of the configurable product SKU values are present in its own simple product’s custom varchar field.

For Example:

Configurable Product 1:

Simple Product1 – Size-M – Qty 0

Simple Product2 – Size-M – Qty 1

Simple Product1 – Size-S – Qty 1

Configurable Product 2:

Simple Product1 – Size-M – Qty 0

Simple Product2 – Size-M – Qty 1

Simple Product1 – Size-S – Qty 1

In the above example I described how we configured the simple products in many configurable products. Our specifications state that the Config Product 2 will only appear on the PLP page when a buyer selects the Size M from the layered navigation.

I have been using a plugin for it but it is not working as expected.

DI.xml

<type name="MagentoCatalogModelLayer">
    <plugin name="vendor_filter_configurable_by_simple" type="VendorAvailabilityPluginLayerPlugin" />
</type>

LayerPlugin.php

<?php

namespace VendorAvailabilityPlugin;

use MagentoCatalogModelLayer;
use MagentoCatalogModelResourceModelProductCollection as ProductCollection;

class LayerPlugin
{
    protected $eavAttribute;

    public function __construct(
        MagentoEavModelConfig $eavAttribute
    ) {
        $this->eavAttribute = $eavAttribute;
    }

    public function afterGetProductCollection(Layer $subject, ProductCollection $collection)
    {
        $attributeCode = 'catalog_code';

        $attribute = $this->eavAttribute->getAttribute('catalog_product', $attributeCode);
        $attrId = (int)$attribute->getAttributeId();

        $linkAlias    = 'vendor_link';
        $varcharAlias = 'vendor_child_varchar';

        $fromParts = $collection->getSelect()->getPart('from');
        if (isset($fromParts[$linkAlias]) || isset($fromParts[$varcharAlias])) {
            return $collection;
        }

        $collection->getSelect()
            ->joinLeft(
                [$linkAlias => $collection->getTable('catalog_product_super_link')],
                "$linkAlias.parent_id = e.entity_id",
                []
            )
            ->joinLeft(
                [$varcharAlias => $collection->getTable('catalog_product_entity_varchar')],
                "$varcharAlias.row_id = $linkAlias.product_id
                 AND $varcharAlias.attribute_id = {$attrId}",
                []
            )
            ->where("e.type_id = 'configurable'")                    // ← Only filter configurables
            ->where("$varcharAlias.value = e.sku")                  // ← Your match condition
            ->distinct(true)
            ->group("e.entity_id");

       
        return $collection;
    }
}