So I am using this code:

$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$IDs = implode(',', $products);
$sql = 'SELECT * FROM `catalog_category_product` WHERE product_id IN ('.$IDs.')';
$results = $connection->fetchAll($sql);
foreach ($results as $result) {
  $this->_backupPositionData[$result['product_id'].'-'.$result['category_id']] = $result['position'];

to acquire from the DB the rows with relevant information and loop through them to map them, etc.

But, as we all know, using direct SQL is not the most beautiful thing. So I wanted to know whether it's possible to use loading of collection somehow to perform the same operation (until the foreach).

The problem here is that I might have to make this fetching for thousands of products, and SQL is the fastest in performance in that case. Is it possible somehow with collection or other object loading technique to do the same ?


I have the IDs of the products (the database saved IDs). What I need is to acquire and map the category id and position to each product id.

In few words - I need the final result array to look like that:


P.S. I am overwriting the _saveProductCategories() function from Mage_ImportExport_Model_Import_Entity_Product and in line 1069 instead of 'position' => 1 I pass certain element of my array. :)

  • Can you explain in a few words what should the result look like? I see from the code that is something related to the product-category relation but It's not clear about the rest of the attributes.
    – Marius
    Apr 14, 2014 at 13:03
  • @Marius - I updated my question. :)
    – Syspect
    Apr 14, 2014 at 13:11

1 Answer 1


I'll assume I understood correctly.

When working with lots of data or batch-processing it, don't be afraid to go low-level. If your code is correct and does what you want it to do, "translating" it to use models and resources could be overkill.

For example, you'd have to load each of your products, call $product->getCategoryIds() on them, and $loadedCategory->getProductsPosition() (see Mage_Catalog_Model_Category::getProductsPosition).

In your case, you'll be better of just using SQL directly. Just make sure that you encapsulate this feature and that it does only this (specialized feature).

Later edit
Just to be clear: your logic is simple and read-only - using SQL directly shouldn’t be a problem. But when it gets more complex (either logically or by number of files or you start saving entities), you should use models and resources.

  • Yep, after review my code, my supervisor said: "Either use OO Magento model and convention or give me a good explanation why you should use the direct SQL". I guess I will quote him your answer. :D
    – Syspect
    Apr 14, 2014 at 15:08
  • It boils down to optimizations: low-level means more speed, but it's detrimental to code design. Usually, people working with Magento fret when seeing sql queries, but I remember someone being in a similar dilemma like you - only with Zend Framework - and Bill Karwin himself (the guy who actually developed Zend_Db_Select in ZF1) told him that, for complex queries or speed/memory-critical parts of the app, it's better not use Zend_Db_Select. It's not meant for that. In a way, this is what I'm telling you.
    – nevvermind
    Apr 14, 2014 at 16:57
  • But could you provide me with an example translation of SELECT * FROM 'catalog_category_product' WHERE 'product_id' IN ('.$IDs.') to usage of collection model (where $IDs will be an array of product IDs as strings)? I tried several things but they either don't give me null or the object does not contain category_id and position. So I just couldn't manage to do it correctly. I would like to have an example just to try the performance and memory usage differences.
    – Syspect
    Apr 15, 2014 at 8:49
  • You can't use a collection with products or category models in your case, because you want a relation, and relations aren't entities (you could make a generic collection - with Varien_Object models - but what's the point?!). To query that table directly, you must use direct sql, like you just did. Indirectly, you'll have loaded models and joined queries. If you want models, you either get that info via the category or the product model - like this.
    – nevvermind
    Apr 15, 2014 at 10:06

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.