How to filter products out with custom created field on catalog_product_entity

Question

I have added 2 fields in db_schema.xml there created on table catalog_product_entity the challenges for me now are I'm using the one field as custom state and this state is the field I need to filter on.

here is my code

$products = $this->productCollection->create()
            ->addAttributeToSelect('*')
            ->addAttributeToFilter([
                [
                    'attribute' => 'status',
                    'eq' => \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED
                ],
            ])
            ->setPageSize($page_size)
            ->setCurPage($current_page);

The problem is coming when I'm adding the next filter like this

->addAttributeToFilter([
                    [
                        'attribute' => 'status',
                        'eq' => \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED
                    ],
[
                        'attribute' => 'my_state',
                        'nin' => 'custom-state'
                    ],
                ])

When I did it this way its tell me the attribute is invalid, but its exists in the table, I have doublet check my database tool

The "my_state" attribute name is invalid. Reset the name and try again

Answer 1

Remove that newly added column filter from attribute filter as it's not attribute it's a field you have added in main catalog entity table and add the field filter condition.

So instead of

->addAttributeToFilter([
                [
                    'attribute' => 'status',
                    'eq' => \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED
                ],
                [
                    'attribute' => 'my_state',
                    'nin' => 'custom-state'
                ],
    ])

Change it to

->addAttributeToFilter([
                [
                    'attribute' => 'status',
                    'eq' => \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED
                ]
            ]);
->addFieldToFilter('my_state', 'nin'=>['custom-state']);

Upvote if it works!