How to rewrite this raw mysql query into magento way?

Question

Can someone please help me convert this raw mysql query into proper magento code.

DELETE a1 FROM catalog_product_entity_decimal as a1 JOIN (SELECT a.value_id FROM catalog_product_entity_decimal as a JOIN catalog_product_entity_decimal as b ON b.entity_type_id = a.entity_type_id and b.attribute_id = a.attribute_id and b.entity_id = a.entity_id and b.store_id = 0 WHERE a.store_id <> 0 and a.value <> b.value) as b1 USING (value_id);

What it's trying to do is delete everything from catalog_product_entity_decimal where value is not equal to default.

I haven't worked with JOINs, so not sure how I can convert it.

UPDATE

As Marius mentioned, I suggested below to my lead but he says we must use joins and delete only different ones because ERP may recreate duplicate records again so running delete all is not good option as it takes up to 0.5 seconds more than conditional delete.

/**
 * Get the resource model
 */
$resource = Mage::getSingleton('core/resource');
/**
 * Retrieve the write connection
 */
$writeConnection = $resource->getConnection('core_write');
/**
 * Retrieve our table name
 */
$table = $resource->getTableName('catalog_product_entity_decimal');
$query = "DELETE FROM {$table} WHERE store_id <> 0";
/**
 * Execute the query
 */
$writeConnection->query($query);

Not sure how to achieve above with magento collection delete method.

• Marius: Why not delete all the values for a store view that is not 0 (default)? I mean, if you leave only the values that are equal to the default value is the same as deleting them and leaving only the default values. The result will be the same but you will have fewer records in your db.
• Damodar Bashyal: @Marius like this? DELETE FROM catalog_product_entity_decimal WHERE store_id <> 0;
• Marius: Yep. Exactly like that. Then all your decimal product attribute will have the 'Use default value' checkbox checked in the admin area. But backup your database in case this is not what you need.
• Damodar Bashyal: @Marius Our lead thinks previous is faster as we are only deleting ones that don't match default store price. so there will be very less rows to delete.
• Marius: There will be less rows to delete, but the ones that will remain will make the selects slower.

Answer 1

I wouldn't actually recommend you do that at all as it contains a subquery. These are very often extremely slow and inefficient and performance even differs between MySQL versions. While on your setup it might be relatively fast, an another it could take 30 seconds or more to execute even with very little data. Also subqueries continue to slow down as more data is being dealt with and as you are dealing with products there is the potential for there being a lot of data.

If you still want to do it anyway then have a look and the Zend_Db_Select documentation. If you pull the select object from your collection ($select = $collection->getSelect()) you can then execute all of the methods here.

• Damodar Bashyal: Thank you for the answer. Would you suggest any other fast and proper method to delete all prices from all stores that are different to default store (0)?