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 '14 at 13:03
  • @Marius - I updated my question. :) – Syspect Apr 14 '14 at 13:11

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.

| improve this answer | |
  • 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 '14 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 '14 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 '14 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 '14 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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