ZF2 - How do I insert into select?

Question

I am trying to perform an insert into select from a table method in zf2.

The following procedure is what I used as an example.

How to perform an INSERT INTO SELECT query in ZF2

My table method is contained in a table class in ZF2 which is a separate table in the db from the tables I am trying to insert into.

public function addCategoryName($val)
    {
        $data = array ( 
            'CategoryName' => $val);

        $this->tableGateway->insert($data);
        $id = $this->tableGateway->lastInsertValue;

        $on = "categoryid = $id";
        $select = new Select('actionitems');

        $select->columns(array('actionitemid', 'categoryid'))->join('categories', $on, array('actionitemid', 'categoryid'), 'cross'); 

        $adapterInsert = new \Zend\Db\Adapter\Adapter(array(
            'driver'   => 'pdo_mysql',
            'database' => 'actionitemmanager',
            'username' => 'username',
            'password' => 'password'
        ));

        $insert = new Insert();
        $insert->into('actionitemcategories');
        $insert->columns(array('actionitemid', 'categoryid'));
        $insert->values($select);
        //insert with select
        $adapterInsert->insertWith($insert);
    }

Neither

$insert->values($select)

or

$insert->select($select)

work...

The first attempt gives an error that the $select must be an array, whereas $insert->select($select) gives an error that select() is not a method of Insert.

How can I get this query to work?

Answer 1

The Zend Framework feature you're referencing was introduced in v2.3:

Zend\Db\Sql\Insert can use a Select object as the value source (INSERT INTO ... SELECT)

I suspect you're using an older version. You can compare Zend\Db\Sql\Insert in v2.2.10 to v2.3.0 to see the different exceptions thrown for invalid arguments.

You should upgrade Zend Framework to v2.3 or greater to use this feature.