Now the category ids are in catalog_category_product_index

For example, if you want return all product categories your query should like this:
SELECT * FROM catalog_category_product_index WHERE product_id = 123

for version 1.4.x

create a local copy and edit Collection.php
e.g.
code\local\Mage\Catalog\Model\Resource\Eav\Mysql4\Product

create a new function using the guts of _applyProductLimitations()
e.g.
public function addCategoriesFilter(array $categories) {
…
$conditions[] = $this->getConnection()->quoteInto(‘cat_index.category_id IN(‘ . implode(“,”, $categories) . ‘)’, “”);
…
}

then make your call
e.g.
$this->_productCollection = Mage::getModel(‘catalog/resource_eav_mysql4_product_collection’)
->setStoreId($storeId)
->addCategoriesFilter(array(9,11,44));

As of writing addCategoryFilter only adds one filter, adding a second overwrites the 1st.

Due to magento 1.4 moving the category column from its original table the top method does not work and at present Ben Incani’s solution is the only viable solution to use 1.4 with this feature.

However doing this sort of patch is not recommended unless you have an upgrade path in place for when magento fixes this issue.

Hi guys, i need the same, how it hard are get some functionality in magento and how easy is to get others features, i would like the magento team had been worked a bit more with object paradigm, cause is incredible how to retrieve a simple thing cross for almost 15 different classes and between them get cross references that became impossible to follow it, i was trying to modify the simple search to get a filter by categories and it was so hard follow a line of thinking, crossing files for everywhere.

if i add this line
->addAttributeToFilter(‘category_ids’,array(‘finset’=>’36,37′));
in my product collection
that throw a error unknown column category_ids in where class .

what to do.

    Unfortunately your code didn’t work for me (Magento v.1.3.2.4), however it lead me to the right direction. I’m getting category ids in array, so this is my solution:
   
    —————
   
    $categoryIDs = $_GET['category'];   
    if(is_array($categoryIDs)){
        $multipleCategoriesSearch = array();
        foreach ($categoryIDs as $value) {
            // prepare array for addCategoryFilter() for searching through multiple categories (prepare for OR operation)
            $multipleCategoriesSearch[] = array(‘attribute’=>’category_ids’,'finset’=>$value); 
        }
        // perform search as OR operation  (ie: if category is 4 OR 11)
        $this->_productCollection->addFieldToFilter(‘category_ids’, array($multipleCategoriesSearch));  //  addFieldToFilter()  ‘OR’ operation   (http://stackoverflow.com/questions/3826474/magento-addfieldtofilter-two-fields-match-as-or-not-and/5988369#5988369)
    }
    else {
      if(is_numeric($categoryIDs)) {   //not an array, single category selected in drop-down box
        $this->_productCollection->addCategoryFilter(Mage::getModel(‘catalog/category’)->load($categoryIDs),true);
      }
    }