cakephp orm query-builder create-table cakephp-3.x

CakePHP 3 - access params of Query object

In CakePHP 3.x I can do this:

$Substances = TableRegistry::get('Substances');
$query = $Substances->find()->where($where_conditions)->select(['id']);
debug($query);

This will show me the Query object.

If I want to get the SQL string I can use debug($query->sql());. This will give me the SQL string with placeholders for any parameters, e.g.

SELECT ... WHERE ... in (:c0,:c1,:c2))

When using debug($query) I can see the values for :c0, :c1, etc:

'params' => [
    ':c0' => [
        'value' => (int) 47,
        'type' => 'smallinteger',
        'placeholder' => 'c0'
    ],
    ':c1' => [
        'value' => (int) 93,
        'type' => 'smallinteger',
        'placeholder' => 'c1'
    ],
    ':c2' => [
        'value' => (int) 845,
        'type' => 'smallinteger',
        'placeholder' => 'c2'
    ],
    ':c3' => [
        'value' => (int) 354,
        'type' => 'smallinteger',
        'placeholder' => 'c3'
    ]
]

However, I cannot access them outside the debug statement. For example $query->params() or $query['params'] doesn't give me the parameters - it will error. I want to be able to pass this array to a custom function, so how can I access it?

It's strange because I can use debug($query->sql()) to get the SQL string as above, and params is just another thing in that object, but doesn't seem to be accessible.

I've read How to get params from query object in CakePHP 3 but think that's a different question as it was to do with not seeing the values in the debug statement due to the default depth that debug would provide.

The reason I want to do this is because I want to be able to do a CREATE TABLE AS query that will write the values of the SELECT statement into a new table (Important: see this link for an example of how that works in vanilla MySQL). I can't figure out how to do that with the ORM in Cake, so was planning on writing a custom function. But I need to be able to access both the SQL as well as the parameters bound so that the query can be executed in my own function.

If you know of a solution where I can use the ORM to do the CREATE TABLE AS query, I'm still interested to know about this. However I would like to know if params are accessible outside debug() as well.

Solution

CakePHP does not provide specific methods for creating such CREATE TABLE AS statements, so you'll have to build that on your own.

Compiling a query as the one shown in your question is simple enough using the query objects sql() method, and as arilia already mentioned, you'll be able to access the parameters bound to that query after is was compiled.

Having the compiled SQL and the associated value binder, you can combine this with a custom raw query to build your CREATE TABLE AS statement. All you need to do is prepare a new statement with the compiled SQL, and attach the value binder via its own attachTo() method.

One thing you might also have to do, is to define custom aliases in your select(), as otherwise you'd end up with columns selected (and created) in the form of Substances_id.

$Substances = TableRegistry::get('Substances');

$selectQuery = $Substances
    ->find()
    ->where($where_conditions)
    ->select(['id' => 'id']); // < create aliases as required

// compile the ORM query, this will populate the value binder
$selectSql = $selectQuery->sql();

// combine table creation SQL and compiled ORM query in a single statement
$createStatement = $Substances
    ->getConnection()
    ->prepare('CREATE TABLE dynamic_table AS ' . $selectSql);

// attach the ORM querys value binder, binding all its values to the given statement
$selectQuery->getValueBinder()->attachTo($createStatement);

$success = $createStatement->execute();

This should create SQL similar to:

CREATE TABLE dynamic_table AS 
    SELECT 
      id AS id
    FROM 
      substances Substances 
    WHERE 
      ...