24

I have a custom table. I want to truncate the table using Magento collection without SQL query.

Hope someone will provide some useful information.

2
  • Do you mean you want to delete some rows based on some condition? Mar 3, 2014 at 12:26
  • No, I need to delete all records and also Auto Increment id start with 1.
    – VijayS91
    Mar 3, 2014 at 12:56

4 Answers 4

19

Magento does not have a support for this (as far as I know).
But you can implement a method in your resource model (not the collection resource model) that will truncate the table.
Something like this:

public function truncate() {
    $this->_getWriteAdapter()->query('TRUNCATE TABLE '.$this->getMainTable());
    return $this;
}

Then you can call it in your code:

Mage::getResourceModel('[module]/[entity]')->truncate();

But this is a very dangerous approach. The truncate statement breaks the transaction, so it cannot be rolled back in case you need to.
I suggest deleteing every entity in the table instead.

$collection = Mage::getModel('[module]/[entity]')->getCollection();
foreach ($collection as $item) {
    $item->delete();
}

The down side of this is that it doesn't reset the increment id of the table. But it's safer.

EDIT.
To update the increment id of the main table you can add a new method in your resource model that does something like this.

public function changeAutoIncrement($increment = 1) {
    $this->_getWriteAdapter()->query('ALTER TABLE '.$this->getMainTable().' AUTO_INCREMENT = '. $increment);
}

Then call your method in the code:

Mage::getResourceModel('[module]/[entity]')->changeAutoIncrement();
7
  • 2
    could you tell me three types of models in magento.thanks in advance Mar 3, 2014 at 12:42
  • 3
    @MeenakshiSundaramR. There is the entity model. Example: Mage_Catalog_Model_Product. There is the resource model. Example: Mage_Catalog_Model_Resource_Product. And there is the collection resource model Mage_Catalog_Model_Resource_Product_Collection.
    – Marius
    Mar 3, 2014 at 12:47
  • 2
    @Marius Can you tell me How to update auto increment id ( its means start with 1 ) available in magento collection.
    – VijayS91
    Mar 3, 2014 at 13:03
  • 2
    @VijayS. See my update to the answer.
    – Marius
    Mar 3, 2014 at 13:08
  • @Marius i have one doubt. then what is Flat model and EAV model? Mar 3, 2014 at 13:12
12
$model = Mage::getModel('[module]/[model]');
$resource = $model->getResource();
$connection = $resource->getReadConnection();
/* @see Varien_Db_Adapter_Pdo_Mysql */
$connection->truncateTable($resource->getMainTable());
$connection->changeTableAutoIncrement($resource->getMainTable(), 1);

Note: MySQL resets autoincrement value to 1 when truncate a table, so it isn't required, but changeTableAutoIncrement may be useful if someone want to have AI starting with another value.


Edit:

This question was tagged with / ... just to complete the answer:

  • changeTableAutoIncrement was added in 1.8.0.1
  • truncateTable was added in 1.6.0.0-alpha1
2

If if want to truncate the custom table in your controller, Cron, and Block so you can first use create the modal/collection on their constructor and use the below structure for the same, and also you can use code as per your requirement.: Below patterns is used in Magento 2.

use Vendor\Module\Model\ModelFactory;
.....
class Collect extends ....
{

protected $_modelFactory;
......

public function __construct(
   .....
   .....
    ModelFactory $modelFactory

) {
    .....
    $this->_modelFcatory = $modelFactory;
    
}
public function execute()
{
        $connection = $this->_modelFactory->create()->getCollection()->getConnection();
        $tableName = $this->_modelFactory->create()->getCollection()->getMainTable();
        $connection->truncateTable($tableName);
 }
}

Hope This helps You.

2
  • 1
    Question was about M1.
    – sv3n
    Dec 16, 2021 at 3:21
  • 1
    Yes, you are right. I have just added the answer has anyone searched the same issue in M2 so it will help it. Thanks Dec 16, 2021 at 13:13
-1

I have create a Model and for some development i had to implement delete method. It is very easy. I also try for conditional delete but i didn't get exact code. But i am using the following code which will fulfill your requirement for conditional delete.

$items = Mage::getModel('custom/csaabandonedcart')->getCollection()
    ->addFieldToFilter('csa_id',Mage::getSingleton('customer/session')
    ->getCustomerId())->addFieldToFilter('customer_id', $retailer)
    ->addFieldToFilter('product_id', $spid);

foreach ($items as $item) {
        $item->delete();
}
2
  • 1
    I am asking about truncate the table. Not to delete the record.
    – VijayS91
    Oct 14, 2015 at 8:10
  • you can do using the following code. if you consider the above code reference. $items->truncate();
    – Sapnandu
    Oct 29, 2015 at 5:59

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.