Why does the truncateTable() method create an empty string for a query and fail to delete rows?

Question

I have the following code attempting to truncate a table. The Joomla documentation makes me believe this will work, but it does not. What am I missing?

$db = JFactory::getDbo();
truncate_query = $db->getQuery(true);
//$truncate_query = 'TRUNCATE ' . $db->quoteName('#__mytable');
$truncate_query->truncateTable($db->quoteName('#__mytable'));
$db->setQuery($truncate_query);
echo $truncate_query;
exit();

The echo statement displays nothing. If I use the line that is commented out to manually generate the SQL, it does work.

The reason I am looking to use the truncateTable() function is that I am trying to include the truncation in a transaction. When I use the manual TRUNCATE statement, the table is still truncated even if another part of the transaction fails. This is annoying since the other statements rely on the success of the truncation; so, if the table is emptied when it shouldn't be there is no data left to run the transaction again.

Answer 1

Update:

It appears that the setQuery() and execute() calls are already inside the function scope...

From https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/driver.php

/**
 * Method to truncate a table.
 *
 * @param   string  $table  The table to truncate
 *
 * @return  void
 *
 * @since   11.3
 * @throws  RuntimeException
 */
public function truncateTable($table)
{
    $this->setQuery('TRUNCATE TABLE ' . $this->quoteName($table));
    $this->execute();
}

So this is how you call/execute the truncation (I just tested this and it works):

class modTrunctestHelper
{
    public function deleteAllRows(){
        JFactory::getDbo()->truncateTable('#__guineapig');
    }
}

I also posted an answer on your replicated Stackoverflow question with different wording.

---

Original post:

I believe this issue/bug should be brought to the attention of the Joomla core developers.

I can confirm that I was able to reproduce the problem.

I ran ->truncateTable() on my #__guineapig table and included some checkpoints along the way:

class modTrunctestHelper
{
    public function deleteAllRows(){
        $db = JFactory::getDbo();
        try {
            $truncate = $db->getQuery(true)
                           ->truncateTable('#__guineapig');
            // $truncate = 'TRUNCATE #__guineapig';
            // $truncate = $db->getQuery(true)
            //                ->delete('#__guineapig');
            echo $truncate->dump();
            $db->setQuery($truncate);
            $db->execute();
            echo $db->getAffectedRows() , 'Rows Of Data Deleted';
        } catch (Exception $e) {
            echo "Syntax Error" , $e->getMessage();
        }
    }
}
$TT = new modTrunctestHelper();
$TT->deleteAllRows();

1. If I wrote: echo $truncate->dump(); the script broke and said:

   Fatal error: Call to a member function dump() on null

2. If I removed ->dump() to avoid the error, then nothing useful can come from executing a null string as a query (duh), the page displayed:

   Syntax Error

3. By running the raw query TRUNCATE #__guineapig (via setQuery() then execute()), all rows were deleted and the auto-incremented index was reset as expected. For the record, $db->getNumRows() is inappropriate and doesn't work; while $db->getAffectedRows() produced a count of 0 because the MySQL manual says:

   Truncation operations do not return a meaningful value for the number of deleted rows.

4. If you are desperate (and I don't think you need to be) to use JDatabase Methods, then you could use delete() which will speak to getAffectedRows() but this is a completely different process with different behaviors. Notably, DELETE queries don't reset the auto-increment number. See my earlier link to the manual to see exactly what TRUNCATE queries do.