Fixing the “Unknown column ‘attribute_id’ in ‘where clause'” Error in Magento 2 (Reliable Fix + SQL Repair)

A product grid that works perfectly until someone types a word into the admin keyword search box — then the grid AJAX request dies with a generic 500 and the log shows SQLSTATE[42S22]: Column not found: 1054 Unknown column 'attribute_id' in 'where clause'. The bare MySQL text underneath is Unknown column 'attribute_id' in 'where clause' (you may also see store_id or value named instead, depending on which missing column MySQL evaluates first).

Up front, the honest framing: you cannot hit this on a stock Magento 2.4.x install. It only appears when a custom product attribute is defined with the self-contradictory combination frontend_input=select + backend_type=static + is_searchable=1. Stock Magento ships no such attribute, and the admin attribute-create form won't normally let you build one. If you are seeing this error, a module you wrote — or one you inherited from a third party — created it. We'll cover why that combination breaks, how to confirm the exact attribute causing it, and a couple of production-safe fixes.

Why Magento 2 throws this error

The crash originates in Magento\CatalogSearch\Model\ResourceModel\Search\Collection::_getSearchInOptionSql(), the method behind the admin product grid's keyword quick-search. When you search a select attribute, this method assumes the attribute's options live in an EAV value table (like catalog_product_entity_int or _varchar) and builds a correlated subquery filtering on attribute_id, store_id and value. It resolves the table via $attribute->getBackend()->getTable() — but for a static attribute, that returns catalog_product_entity itself, a plain table that has none of those columns. The generated SQL references fields that don't exist, and MySQL rejects it with error 1054.

This is not a Magento core defect — core's own static attributes (sku, url_key) use text/varchar input and route through the safe getCILike branch, and core even ships a patch (UpdateUrlKeySearchable) making a static attribute searchable without issue. The error is reachable only via a hand-built static + select + searchable attribute. The usual ways a codebase ends up with one:

• A custom attribute was created with frontend_input=select but backend_type=static, so its raw key lives as a real column on catalog_product_entity instead of in an EAV value table — while still presenting as a dropdown.
• is_searchable=1 was set on it — often copied from another definition, or toggled to force the field into the search index — without realizing that flag also enrolls the attribute in the admin grid's option-matching path.
• The failure is intermittent. _getSearchInOptionSql() first looks up the typed term against the attribute's option labels; the broken subquery is built only when the term matches a real option label stored in eav_attribute_option_value. Most searches are no-ops, which is why it survives setup:upgrade, reindex, and smoke tests.
• If the attribute is backed by a custom PHP source_model (its options come from getAllOptions() rather than the database), there are zero eav_attribute_option rows and the broken path is never reached at all — so the same misconfiguration may sit latent until someone adds real DB-backed options.

1. Confirm the offending attribute's storage and searchable flag

Start with the metadata tables, not the code. This query surfaces every static-backed select attribute flagged searchable — each row is a latent crash:

SELECT a.attribute_id, a.attribute_code, a.frontend_input, a.backend_type, c.is_searchable
FROM eav_attribute a
JOIN catalog_eav_attribute c ON a.attribute_id = c.attribute_id
JOIN eav_entity_type e ON a.entity_type_id = e.entity_type_id
WHERE e.entity_type_code = 'catalog_product'
  AND a.backend_type = 'static'
  AND a.frontend_input = 'select'
  AND c.is_searchable = 1;

You should see one or more rows, e.g. supplier_ref | select | static | 1. If the query returns zero rows, this error is not your problem — look elsewhere. Note each attribute_code; those are what drive _getSearchInOptionSql() to emit invalid SQL. To confirm the attribute actually has DB-backed options (the precondition for the crash to fire), check the option count:

SELECT COUNT(*) FROM eav_attribute_option WHERE attribute_id = <attribute_id>;

If the count is 0, the attribute is source-model-backed and won't crash yet — but it's still misconfigured and should be fixed before someone adds real options.

2. Disable searchability on the custom attribute via an idempotent data patch

To be clear about what this fixes: it does not patch Magento core. It corrects your own custom attribute's metadata so the attribute stops entering the option-search path. Don't hand-edit the table — add a Setup/Patch/Data patch in your module (Vendor\Module) implementing DataPatchInterface, with ModuleDataSetupInterface and EavSetupFactory injected:

public function apply(): self
{
    $eavSetup = $this->eavSetupFactory->create(['setup' => $this->moduleDataSetup]);
    $eavSetup->updateAttribute(
        \Magento\Catalog\Model\Product::ENTITY,
        'supplier_ref',
        'is_searchable',
        0
    );
    return $this;
}

Setting is_searchable=0 removes the attribute from the option-search path entirely, so _getSearchInOptionSql() never builds a subquery for it. A patch makes the change versioned, idempotent, and deploy-safe across every environment. The durable root-cause remedy, though, is the rule itself: never define a static-backed select attribute as searchable. If you genuinely need the field in the search index, route it through an explicit data provider (Step 5) instead of is_searchable.

3. Apply the patch

Run the patch and flush:

php bin/magento setup:upgrade && php bin/magento cache:flush

Confirm the patch registered as applied — grep the patch list for your class name:

php bin/magento setup:db-schema:upgrade 2>/dev/null; \
  mysql -e "SELECT patch_name FROM patch_list WHERE patch_name LIKE '%DisableSupplierRefSearchable%';" your_database

You should see your patch's fully-qualified class name listed, and catalog_eav_attribute.is_searchable for that attribute is now 0. Re-run the Step 1 query to confirm it no longer appears.

4. Verify the admin grid search no longer crashes

Tail the relevant logs while you reproduce the search:

tail -f var/log/exception.log var/log/system.log

Open Admin > Catalog > Products, type a term that previously triggered the 500 (one matching a real option label of the offending attribute), and confirm matching products return. Then scan the logs for the signature to be sure it's gone:

grep -nE 'SQLSTATE\[42S22\]|Unknown column' var/log/exception.log var/log/system.log

You should see no new SQLSTATE[42S22] or Unknown column entries after your test — proof the option-search path is no longer invoked for that static column.

5. (Optional) Keep the field present in the index as a typed, filterable field

If the field needs to live in the search index — for filtering, aggregations, or PLP resolvers — don't re-enable is_searchable. Inject it into the indexed documents explicitly through a custom AdditionalFieldsProviderInterface data provider plus a field mapping:

// Batch provider (AdditionalFieldsProviderInterface::getFields):
// pull the static column straight from the entity table
// SELECT entity_id, supplier_ref FROM catalog_product_entity WHERE entity_id IN (...)
// then in your field mapper:
$result['supplier_ref'] = ['type' => 'integer'];

The field now appears in the index documents with your chosen type, independent of the is_searchable flag. Be precise about what this buys you: an integer mapping makes the field filterable and aggregatable, not text-searchable. A storefront keyword query will not match against it unless you additionally register it in the search request configuration and weighting. For a numeric key or reference column — the typical reason a field ends up static-backed — filterable is exactly what you want. Skip this step entirely if the field never needs to be in the index.

When this points to a deeper problem

A single patch fixes the symptom, but the mismatch usually signals something systemic:

• Attribute metadata gets treated as independent flags, but frontend_input, backend_type and is_searchable are coupled — a select input implicitly promises an EAV value table, and nothing validates that invariant at attribute-creation time. A static-backed select is a contradiction the core doesn't guard against.
• is_searchable is overloaded: it controls admin grid quick-search and contributes to search-index inclusion. We've seen this most often when a team flips it on purely to push a field into the index and silently re-arms the grid crash. Note, though, that index inclusion is an OR across several flags — is_searchable, is_visible_in_advanced_search, is_filterable, is_filterable_in_search, used_for_sort_by (see Product\Attribute\Collection::addToIndexFilter()). is_searchable is just the one that happened to be set; any of the others would keep a field indexed without arming the grid path.
• Static-backed columns are a power-user shortcut that bypasses the assumptions baked into select handling. Mixing static storage with select presentation is undefined territory.

Audit every catalog_product attribute (and other EAV entities) for backend_type='static' + an option-style frontend_input + any of the index-inclusion flags. Treat is_searchable as a search-behavior flag only, and route "must be in the index" requirements through an explicit field provider with the right type. Resetting one attribute is a patch; the audit is the durable fix.

You now have the query to find these attributes, a safe patch to neutralize them, and a clean path to keep real fields in the index — enough to clear the error and stop it from coming back.