Load only certain attributes and map them, by collection

Question

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 ?

UPDATE:

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:

array["product_id-category_id"]["position"]

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. :)

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.