Magento 1.8 – Translate Direct SQL Query into Collection Model Usage

collection;magento-1.8MySQLproduct-collection

I've got this query:

SELECT * FROM 'catalog_category_product' WHERE 'product_id' IN ('.$IDs.')

where $IDs I get the following way: $IDs = implode(',', $products); and $products is an array of product ids.

Now I want to do the same thing, but through the use of a model collection. I've got so far with these examples:

$collection = Mage::getModel('catalog/product')->getCollection();
        $collection->addAttributeToSelect('product_id');
        $collection->addAttributeToSelect('category_id');
        $collection->addAttributeToSelect('position');
        $collection->addFieldToFilter(array('attribute'=>'product_id','in'=>array($products)));

and 

$collection = Mage::getModel('catalog/product')->getCollection();
        $collection->addAttributeToSelect('product_id');
        $collection->addAttributeToSelect('category_id');
        $collection->addAttributeToSelect('position');
        $collection->addFieldToFilter(array(
                array('attribute'=>'product_id','in'=>array($products)),
        ));

But I get exception on the last function call, in both cases.

P.S. I need to acquire these 3 attributes at once (as the SQL query would do), but not iterating for each value in $products because I am dealing with thousands of products.

Best Answer

Here's a starting point:

$collection = Mage::getModel('catalog/product')
                ->getCollection()
                ->addIdFilter($productIdsArr)
                ->joinTable(
                    array('prod_categ_assoc' => 'catalog/category_product'), 
                    'product_id=entity_id', 
                    array('categ_id' => 'category_id', 'position'), 
                    null, 
                    'left'
                );

 $result = array();

 // play with the joined columns
 foreach ($collection as $product) {
    $key = $product->getId() . "-" . $product->getCategId();
    $result[$key] = $product->getPosition();
 }

 var_dump($result);

 // check the resulting SQL with this
 var_dump($collection->getSelect()->__toString());

You may have noticed that I used categ_id as an alias to category_id. That's because Mage_Catalog_Model_Product::getCategoryId() is already implemented (and probably used in other places). Better stay safe and use our own attribute - categ_id.

You'll be interested in Mage_Catalog_Model_Resource_Product_Collection::addIdFilter() and Mage_Eav_Model_Entity_Collection_Abstract::joinTable().

PS: You should check the collection query string before the foreach, too. It'll be very different from the one after the loop. Magento hooks in after collection loading with some other logic. Just a tip.

Update

I tested on a vanilla installation, and, if you have a product assigned to more than one category, I think the above query will definitely fail with Object with the same id "<id>" already exist. The collection ID field is the product ID, and that must be unique throughout the collection.

Some 10 minutes later...

Yup, it throws. This is the best I could come up with so that you don't see any SQL in there:

// $collection is the above collection object
$resultArr = $collection->getConnection()->fetchAll($collection->getSelect());

But this collection is not properly set - other SQL logic is applied when you load the collection (remember my tip?), but you can't load it now, because it will throw. Still want convention?

Related Topic