CakePHP 3: Best Practice for Temporary SQL Tables

Question

Dear CakePHP 3 developers,

I'd like to use SQL's Temporary Tables in a CakePHP 3.4.13 project for a single run through a script. Going through Cake's documentation, there seems no direct way to tell CakePHP my desire. How would I best go about it, then?

I've prepared a Table in src/Model/Table/TempItemsTable.php:

namespace App\Model\Table;
use Cake\ORM\Table;

class TempItemsTable extends Table
{
    public $fields = [
        'id' => ['type' => 'integer'],
        'con' => ['type' => 'string', 'length' => 255, 'null' => false],
        '_constraints' => [
            'primary' => ['type' => 'primary', 'columns' => ['id']]
        ]
    ];
    public function initialize(array $config)
    {
        // $this->setTable(null);
    }
}

The idea to use $fields to tell CakePHP the desired table schema comes from a possibly unrelated documentation for Test Fixtures.

But how do I tell CakePHP not to look for an actual table in the database? The uncommented line $this->setTable(null); was my poor attempt at that, which is supposedly similiar to the right way in earlier versions of CakePHP, but according to version 3.x documentation, setTable() doesn't accept null, while table() does, but it's deprecated as of 3.4 and also didn't change anything.

Finally, of course, I get this exception as soon as I try to access this "table" in a controller via $temp = TableRegistry::get('TempItems');:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mydatabase.temp_items' doesn't exist

Help, I'm stuck. :(

ndm · Accepted Answer · 2018-03-21T09:16:03.617

There's no need to tell it to not look for the table, actually that's the opposite of what you want to do, given that you eventually want to access it.

The table class should basically be configured as usual, and you should create the temporary database table before the application causes it to be accessed. You can either write the raw table creation SQL manually, or generate it from a \Cake\Database\Schema\TableSchema instance, which supports temporary tables.

You can either explicitly create the schema object:

$schema = new \Cake\Database\Schema\TableSchema('temp_items');
$schema
    ->addColumn('id', ['type' => 'integer'])
    ->addColumn('con', ['type' => 'string', 'length' => 255, 'null' => false])
    ->addConstraint('primary', ['type' => 'primary', 'columns' => ['id']])
    ->setTemporary(true);

$TableObject->setSchema($schema);

or let the table object generate it, using your fields definition array:

$TableObject->setSchema($TableObject->fields);
$schema = $TableObject->getSchema()->setTemporary(true);

You can then generate the table creation SQL from the schema object and run it against the database:

$connection = $TableObject->getConnection();
$queries = $schema->createSql($connection);

$connection->transactional(
    function (\Cake\Database\Connection $connection) use ($queries) {
        foreach ($queries as $query) {
            $stmt = $connection->execute($query);
            $stmt->closeCursor();
        }
    }
);

$queries would be an array of SQL commands required to create the table, something along the lines of:

[
    'CREATE TEMPORARY TABLE `temp_items` (
        `id` INTEGER AUTO_INCREMENT,
        `con` VARCHAR(255) NOT NULL,
        PRIMARY KEY (`id`)
    )'
]

Note that if you do not assign the schema to the table object, you could run into caching problems, as the cached schema wouldn't match anymore when you change the table definition and do not clear the cache.

CakePHP 3: Best Practice for Temporary SQL Tables

1 Answers1

Linked