[FIXED] Doctrine migrations: how to retrieve value right after insert

Issue

I have a doctrine migration class with standard up method, like this:

$this->addSql("INSERT INTO `mytable` (`name`) VALUES ('myvalue')");

        $res = $this->em->createQueryBuilder()
            ->select('a')
            ->from(MyEntityClass::class, 'a')
            ->where("a.name = :name")
            ->setParameter('name', 'myvalue')
            ->getQuery()
            ->getResult();
        $id = $res[0]->getId();

        //some other operation using $id...
        

running this code I got an error: Notice: Undefined offset: 0

if I manually check the db, the value was not inserted.

However, if I commented out the block with the select query, the value is correctly saved to the table.

Looks to me the query in addSql are all executed at the end of the script, is there a way to retrieve a value right after it was saved on the db?

Solution

This is because addSql is not executed immediately. Try to do the following:

$this->connection->executeUpdate("INSERT INTO `mytable` (`name`) VALUES ('myvalue')");
// and then
$lastId = $this->connection->fetchColumn('SELECT LAST_INSERT_ID()');

P.S. And don't use query builder inside migrations(it's a bad practice). Because your entity can be changed in the future(or even removed).

Answered By - Petro Popelyshko