Create a temporary DB table based on some dynamic parameters

Need Help
1

Hello,
I’m using cakePHP version 3.7 and I’d like, from a controller, to create a temporary database table build by using some user inputs
The table fields are pre-defined, just the query to build it has, in its SELECT part, the user inputs (I know the statement has to be prepared before execution)
I’ve tried some attempts but always got exception saying that table does not exist
Can someone please sketch the main ideas to gain this desired result?
If I can’t manage to define the temporary table otherwise, I think I will create it by using a stored procedure and after that I’ll access the table “normally”
Thanks
Ruth

2

The following page has a lot of good information:

https://book.cakephp.org/3.0/en/orm/schema-system.html

Here is an example that hopefully proves helpful:

// define temp table; name the table anything you like, so long as it's unique
$schema = new TableSchema( 'temp_data' );

// example temp table schema; add any fields you need
$schema->addColumn( 'id', [ 'type' => 'integer' ] )
	->addColumn( 'user_input_a', [ 'type' => 'string', 'length' => 255, 'null' => true ] )
	->addColumn( 'user_input_b', [ 'type' => 'string', 'length' => 255, 'null' => true ] )
	->addConstraint( 'primary', [ 'type' => 'primary', 'columns' => [ 'id' ] ] )
	->setTemporary( true );

// get 'default' database connection
$connection = ConnectionManager::get( 'default' );

// create and drop SQL statements
$create_sql = $schema->createSql( $connection );
$drop_sql = $schema->dropSql( $connection );

// create temp table
foreach ( $create_sql as $sql ) {
	$connection->execute( $sql );
}

// insert some data
$connection->insert( 'temp_data', [
	'user_input_a' => $some_user_data_a,
	'user_input_b' => $some_user_data_b
] );

// retrieve some data
$user_data = $connection->newQuery()
	->select( '*' )
	->from( 'temp_data' )
	->where( [ 'user_input_a' => $some_value_to_find ] )
	->execute()
	->fetchAll( 'assoc' );

// show the retrieved data
debug( $user_data );

// drop temp table
foreach ( $drop_sql as $sql ) {
	$connection->execute( $sql );
}

Note: the above example was used with PostgreSQL; some syntax might differ slightly.

3

Solved this way:

I've created a table in the database corresponding to my desired temporary table
I've run the cake bake model command to let cakePHP generate the entity and the table class
I've dropped the table from the DB

in the controller:

4.a I've prepared and executed the statement to create the temporary table

4.b I've loaded the model and used it

The issue I had was (what a shame!) a typographical error, because at a certain moment in a join query I used the name of the model instead of the name of the table in the conditions array.

I.e. I was asking wrongly for:

->join([
                    'table' => 'tempLogs',
                    'type' => 'INNER',
                    'conditions' => 'tempLogs.Job = Istanze.Job',
                ])

instead of the correct:

->join([
                    'table' => 'temp_logs',
                    'type' => 'INNER',
                    'conditions' => 'temp_logs.Job = Istanze.Job',
                ])

temp_logs is the temporary table name, TempLogs is the name of the table class