cakephpcakephp-3.0

Mixing values in `INSERT INTO ... SELECT` query


Say I want to insert a comment, the comments table needs a customer_id but I only have email. I do not want to select the customer in a separate query beforehand.

The INSERT INTO ... SELECT query from orm/query-builder.html#inserting-data looks like what I want exactly.

But how do I provide values outside of what gets selected in the first part of the query?

  1. I tried:

     $select = $customersTable->find()
         ->select(['customer_id'])
         ->where(['email' => 'username@example.com']);
     $query = $this->query()
         ->insert(['customer_id', 'data'])
         ->values($select)
         ->values(['data' => 'test 123'])
         ->execute();
    

    I got: You cannot mix subqueries and array data in inserts

  2. I tried:

     $select = $customersTable->find()
         ->select(['customer_id', '"test" AS `data`'])
         ->where(['email' => 'username@example']);
     $query = $this->query()
         ->insert(['customer_id', 'data'])
         ->values($select)
         ->execute();
    

    I got:

    Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`` AS `Customers__"test" AS `data`` FROM `customer` `Customers` ' at line 1
    
    INSERT INTO `comments` (`customer_id`, `data`) SELECT `Customers`.`customer_id` AS `Customers__customer_id`, `Customers."test" AS `data`` AS `Customers__"test" AS `data`` FROM `customer` `Customers` WHERE `email` = :c0
    

Please help


Solution

  • Custom aliased fields in the SELECT clause can be defined via the regular key => value array syntax, where the key will be used as the alias.

    Also make sure to let the driver quote the data, as it will be inserted as-is, and thus would otherwise be prone to possible SQL injections!

    $select = $customersTable->find()
        ->select([
            'customer_id',
            'data' => $customersTable->getConnection()->quote('test')
        ])
        ->where(['email' => 'username@example']);
    

    See also