Magento 1.8 – How to Remove Product from Collection by Custom Product Attribute

Ok, I try to explain my solution, I give here most important points, but you should have some experiences like magento layouts, blocks, create custom module.
1. Create attribute which you assumed in your question.
2. Configure your products attaching them to particular category and attribute value created above.
3. Create custom block given in example (/app/code/local/Some/Module/Block/Catalog/Navigation.php):

class Some_Module_Block_Catalog_Navigation extends Mage_Core_Block_Template
{
    protected $_attributeCode = 'item_branch';

    /**
     * @return Mage_Catalog_Model_Resource_Eav_Attribute
     */
    public function getAttribute()
    {
        $attribute = Mage::getModel('catalog/resource_eav_attribute')
            ->loadByCode(Mage_Catalog_Model_Product::ENTITY, $this->_attributeCode);
        return $attribute;
    }

    protected function _construct()
    {
        $this->addData(array(
            'cache_lifetime' => false,
            'cache_tags'     => array(Mage_Catalog_Model_Category::CACHE_TAG),
        ));
    }

    public function getCacheKeyInfo()
    {
        $shortCacheId = array(
            'CATALOG_NAVIGATION',
            Mage::app()->getStore()->getId(),
            Mage::getDesign()->getPackageName(),
            Mage::getDesign()->getTheme('template'),
            'template' => $this->getTemplate(),
            'name'     => $this->getNameInLayout(),
            $this->_attributeCode
        );

        return $shortCacheId;
    }

    public function getCategoryUrl($category)
    {
        if ($category instanceof Mage_Catalog_Model_Category) {
            $url = $category->getUrl();
        } else {
            $url = $this->_getCategoryInstance()
                ->setData($category->getData())
                ->getUrl();
        }

        return $url;
    }

    public function getTree()
    {
        $attributeId = $this->getAttribute()->getId();
        $websiteId   = Mage::app()->getWebsite()->getId();

        $resource   = new Mage_Catalog_Model_Resource_Setup('core_setup');

        $categories = Mage::getModel('catalog/category')->getCollection();

        $categories
            ->addFieldToSelect(array('category_id' => 'entity_id', 'parent_id'))
            ->getSelect()
            ->join(array('cp' => $resource->getTable('catalog_category_product')), 'cp.category_id=main_table.entity_id', 'product_id')
            ->join(array('pw' => $resource->getTable('catalog_product_website')), "pw.product_id=cp.product_id AND  pw.website_id = $websiteId", 'website_id')
            ->join(array('av' => $resource->getTable('catalog_product_entity_int')), "av.entity_id=pw.product_id AND  av.attribute_id = $attributeId", array('option_id' => 'value'))
            ->join(array('ov' => $resource->getTable('eav_attribute_option_value')), 'ov.option_id=av.`value` AND  ov.store_id = 0', array('option_value' => 'value'))
            ->order(array('option_value', 'category_id'));

        $query = $categories->getSelectSql(true);

        $data = $resource->getConnection()->fetchAll($query);
        $tree = array();

        foreach ($data as $row) {
            $optionValue = $row['option_value'];
            if (!isset($tree[$optionValue])) {
                $tree[$optionValue] = array(
                    'label'      => $optionValue,
                    'categories' => array()
                );
            }
            $parentCategory = $row['parent_id'];
            if (!isset($tree[$optionValue]['categories'][$parentCategory])) {
                $tree[$optionValue]['categories'][$parentCategory] = array(
                    'category_id'    => $parentCategory,
                    'sub_categories' => array(),
                );
            }
            $categoryId = $row['category_id'];
            $tree[$optionValue]['categories'][$parentCategory]['sub_categories'][$categoryId] = $categoryId;
        }

        return $tree;
    }
} 

I created this block to achieve caching of my custom category navigation, because of to build this navigation it is required to load extra data from db. But you can use tree build logic in code given above without using block.

4.Add this navigation to frontend using local.xml layout (/app/design/frontend/default/default/layout/local.xml):

<?xml version="1.0"?>
<layout version="0.1.0">
    <catalog_category_default translate="label">
        <reference name="left">
            <block type="some_module/catalog_navigation" name="catalog.leftnav" before="currency"
                   template="catalog/navigation/myleft.phtml"/>
        </reference>
    </catalog_category_default>

    <catalog_category_layered translate="label">
        <reference name="left">
            <block type="some_module/catalog_navigation" name="catalog.myleft"
                   template="catalog/navigation/myleft.phtml"/>
        </reference>
    </catalog_category_layered>
</layout> 

5.Create navigation block template (/app/design/frontend/default/default/template/catalog/navigation/myleft.phtml):

<?php /* @var $this Some_Module_Block_Catalog_Navigation */ ?>
<?php $tree = $this->getTree(); ?>
<?php $categories = Mage::getModel('catalog/category')->getCollection()->addAttributeToSelect('name'); ?>
<?php $_count = count($tree) ?>
<?php if ($_count): ?>
    <div class="block block-layered-nav">
        <div class="block-title">
            <strong><span><?php echo $this->__('Browse By ') . $this->getAttribute()->getFrontendLabel() ?></span></strong>
        </div>
        <div class="block-content">
            <?php foreach ($tree as $label => $optionCategory): ?>
                <div>
                    <h5><?php echo $label ?></h5>
                    <ul>
                        <?php foreach ($optionCategory['categories'] as $parent): ?>
                            <?php $_category = $categories->getItemById($parent['category_id']) ?>
                            <li>
                                <a href="<?php echo $this->getCategoryUrl($_category) ?>"><?php echo $this->htmlEscape($_category->getName()) ?></a>
                                <ul>
                                    <?php foreach ($parent['sub_categories'] as $categoryId): ?>
                                        <?php $_category = $categories->getItemById($categoryId) ?>
                                        <li>
                                            --<a href="<?php echo $this->getCategoryUrl($_category) ?>"><?php echo $this->htmlEscape($_category->getName()) ?></a>
                                        </li>
                                    <?php endforeach ?>
                                </ul>
                            </li>
                        <?php endforeach ?>
                    </ul>
                </div>
            <?php endforeach; ?>
        </div>
    </div>
<?php endif; ?>

6.You can fix design of this menu adding some css classes and rules as you want.

By the way your attribute type should be select. Try to understand the logic and adjust it to your store, this gives me perfect result, maybe it not works via copy/paste without understanding and adjusting the code. See my result:

P.S: maybe this solution has some bugs or issues which I didn't sense, comments will be appreciated.

The catalog_product_index_eav table has a foreign key contraint named

FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID

Looking at this table's definition

CREATE TABLE `catalog_product_index_eav` (
  `entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `value` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Value',
  PRIMARY KEY (`entity_id`,`attribute_id`,`store_id`,`value`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID` (`entity_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID` (`attribute_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID` (`store_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE` (`value`),
  CONSTRAINT `FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product EAV Index Table';

we can see the foreign key definition is

CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` 
FOREIGN KEY (`entity_id`) 
REFERENCES `catalog_product_entity` (`entity_id`) 
ON DELETE CASCADE ON UPDATE CASCADE

This means for every entity_id row in catalog_product_index_eav, there needs to be an identical, corresponding entity_id value in catalog_product_entity.

The root of your problem is for some reason (either a rouge extension, errors caused by randomly typing in SQL from the internet, or performing data updates with disabled indexes), Magento's indexing attempts to update data in catalog_product_index_eav that violates this rule. The next step is identifying what Magento's doing so you can fix the data.

If we look at your call stack, this looks like a good place to start debugging

#10 app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): 
Mage_Index_Model_Resource_Abstract->syncData()

Jumping to that source file, we see the following bit of code

public function syncData()
{
    $this->beginTransaction();
    try {
        /**
         * Can't use truncate because of transaction
         */
        $this->_getWriteAdapter()->delete($this->getMainTable());
        $this->insertFromTable($this->getIdxTable(), $this->getMainTable(), false);
        $this->commit();
    } catch (Exception $e) {
        $this->rollBack();
        throw $e;
    }
    return $this;
}

As part of its indexing process, Magento tries to sync data from an "index table" (getIdxTable), to a "source" (getMainTable) table.

public function insertFromTable($sourceTable, $destTable, $readToIndex = true)
{
    //...
}

For this particular index, the index table is catalog_product_index_eav_idx, and the source table is catalog_product_index_eav.

Note: Be careful with your terminology around here, things are confusingly named. The "Source" table is the table we're copying to. (I believe it's called the source table because it's the "source" a normal Magento system will query from when it needs information)

So, Magento is trying to sync a row from catalog_product_index_eav_idx to the table catalog_product_index_eav. However, this causes the previously mentioned foreign key error. This leads us to two possible conclusions

1. The catalog_product_index_eav_idx has entity_id rows that do not exist in catalog_product_entity.

2. The catalog_product_index_eav table has (through previous manipulation with index checks turned off) entity_id rows that do not exist in catalog_product_entity.

So, your mission here is to figure out which entity_id rows in catalog_product_index_eav and catalog_product_index_eav_idx don't exist in catalog_product_entity, and manually delete said rows (from catalog_product_index_eav and catalog_product_index_eav_idx).

If it were me, and my catalog_product_entity table wasn't too large, I'd start with the following queries (these are untested, as I don't have any Magento tables with the above invalid data states)

SELECT * 
FROM catalog_product_index_eav_idx 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

SELECT * FROM catalog_product_index_eav 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

Good luck!