Where "use in layered navigation" attribute property is stored in the database?

Question

I am looking for where (what table) in the Magento 1.9.0.3 database this attributes property "use in layered navigation" is stored. I actually new it but now it takes a lot of time to remember. Maybe someone can help out right away? Thanks.

Searched in eav_attribute, eav_attribute_option. No luck yet :)

I need to select all the attributes ids with "use in layered navigation" enabled programmatically.

Answer 1

That value stored in catalog_eav_attribute table.

If you simply want to get all attributes that are set as filterable via the admin section then you need to filter on the table catalog_eav_attribute where the column is_filterable is true:

/** @var Mage_Catalog_Model_Resource_Product_Attribute_Collection $attributeCollection */
$attributeCollection = Mage::getResourceModel('catalog/product_attribute_collection');
$attributeCollection->addFieldToFilter('is_filterable', true);

Answer 2

It's catalog_eav_attribute.is_filterable: (used information_schema.columns to find out)

This select works for me:

select 
  a.attribute_id
, a.attribute_code
, ca.is_filterable 
from
  catalog_eav_attribute ca
inner JOIN  
  eav_attribute a
on  
  a.attribute_id = ca.attribute_id
where
  is_filterable in 
  ( 
    1 -- filterable(with results)
  , 2 -- filterable(with no results)
  )