In Magento, I need to run a query to find out which categories have a Static Block assigned. I know static blocks are stored in
cms_block, and also where categories are stored. However, what is the table that joins these two in the database? Or is it a foreign key field in the category table?
Best How To :
Categories are EAV model, so, you should indeed look at some table to join.
Here is the request you are looking for :
select cat.*, cms.* from `catalog_category_entity` as cat
join `catalog_category_entity_int` as ci on cat.entity_id = ci.`entity_id`
join `eav_attribute` as att on att.`attribute_id` = ci.`attribute_id`
join `cms_block` as cms on cms.`block_id` = ci.`value`
where att.`attribute_code` = 'landing_page'
Alan Storm, has a really great article (as usual) on his blog if you want to dig deeper into Magento EAV structure : http://alanstorm.com/magento_advanced_orm_entity_attribute_value_part_1