1

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.

2
  • Use addAttributeToFilter instead of addFieldToFilter.
    – Marius
    Apr 15, 2014 at 10:11
  • 1
    Hello again, Syspect. So your supervisor wants convention, eh? No problem. Just one thing: you can't get information from catalog_category_product using a product collection directly. addAttributeToFilter adds a product attribute to the collection. position is not one of them.
    – nevvermind
    Apr 15, 2014 at 20:37

2 Answers 2

1

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?

2
  • I tested with products being in more than categories and it was fine. Thanks a lot for the help! Me... I don't want the convention, but... I don't have the final decision here... :D
    – Syspect
    Apr 16, 2014 at 8:53
  • Not sure exactly how you tested it, because I did, too, and I got that exception I was talking about. You should test some more, but, if it works... And avoid programming dogmatism for the sake of it. That's "cult programming". Anyway, glad to be of help.
    – nevvermind
    Apr 16, 2014 at 9:06
0

You have to replace products_id with entity_id.

$collection = Mage::getModel('catalog/product')->getCollection()
->addAttributeToSelect(*)
->addFieldToFilter( 'entity_id',array('in' => $products) );

I suppose that $products is an array. You can find a quick reference on product collection here : http://www.creare.co.uk/magento-product-collection-bible

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.