9

I want to truncate my Database Table, with CakePHP Model, I have used $this->Model->deleteAll code for the same, and it works fine.

Now, What i want is, next time whenever my new records are inserting, it should start ID with 1 only, which does not work with deleteAll function, so Is there any default CakePHP Syntax to make table Truncate ?

Let me know !

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Aditya P Bhatt
  • 21,431
  • 18
  • 85
  • 104

3 Answers3

16

NOTE: This answer is valid only up to CakePHP 1.3. I haven't used this on the never versions so I've no idea whether it works.

deleteAll only deletes the data, it does not truncate the table.

You'll need to call the query() method. 

$this->Model->query('TRUNCATE TABLE table_name_in_mysql;')

http://book.cakephp.org/view/1027/query

JohnP
  • 49,507
  • 13
  • 108
  • 140
  • Only one warning -- it`s mysql-specific and not in all DBMS we could have truncate. [It was officially introduced in the SQL:2008 standard.](http://en.wikipedia.org/wiki/Truncate_(SQL)) – gaRex May 12 '11 at 09:57
  • 2
    Shouldn't it be `$this->Model->query('TRUNCATE table tablename;')` – trante Nov 14 '12 at 18:42
  • @trante, I try like your way, but query can't execute. What should I do? – Vy Do Apr 27 '15 at 04:45
  • @DoNhuVy This answer is quite a few years old and I haven't worked with Cake in a long time. I'm sorry but someone who's worked on the new CakePHP will need to weigh in. – JohnP Apr 27 '15 at 11:07
  • It does work on Mysql with CakePHP 2.X "tablename" should be the name on mysql table, not the alias, nor the Model name. – Jorge Ramirez Dec 16 '15 at 05:42
3

@JohnP's doesn't take into account the table's prefix as configured in database.php. Here's a slightly more robust approach.

The DboSource object attached to each model already has a fullTableName() method that does exactly what we need.

First, create Model/AppModel.php if it doesn't already exist, and add this method to it:

/**
 * fullTableName
 *
 * Provides access to the Model's DataSource's ::fullTableName() method.
 * Returns the fully quoted and prefixed table name for the current Model.
 *
 * @access public
 * @param boolean $quote Whether you want the table name quoted.
 * @param boolean $schema Whether you want the schema name included.
 * @return string  Full quoted table name.
 */
public function fullTableName($quote = true, $schema = true) {
    $datasource = $this->GetDataSource();
    return $datasource->fullTableName($this, $quote, $schema);
}

With this, you can get the full table name, including the prefix, for any Model in your Cake app:

$this->Model->fullTableName();

We can do better though. Next, add this method to AppModel too:

/**
 * truncate
 *
 * Truncates ALL RECORDS from the Model it is called from! VERY DANGEROUS!
 * Depends on the ::fullTableName() method to concatenate the configured
 * table prefix and table name together and quote the whole bit properly.
 *
 * @access  public
 * @return  mixed
 */
public function truncate() {
    $fullName = $this->fullTableName();
    $q = 'TRUNCATE TABLE %s';
    return $this->query(sprintf($q, $fullName));
}

Now you can (easily, so be careful!) truncate any Model in your app like so:

$this->Model->truncate();

And if you ever need to adjust the SQL query to match a different DataSource, you can do so in a central place in your app. You can also easily override the truncate() method in specific models if they use a different DataSource with a different syntax.

beporter
  • 3,740
  • 3
  • 37
  • 45
0

A database driver agnostic solution for CakePHP 3:

Create an AppTable.php, and make all your tables extend this one.

Add this function to it:

public function truncate()
{
    $truncateCommands = $this->schema()->truncateSql($this->connection());
    foreach ($truncateCommands as $truncateCommand) {
        $this->connection()->query($truncateCommand);
    }
}

Then simply call $table->truncate(); and it should truncate the table regardless of which database driver you use.

btaens
  • 27
  • 2