How to detect the last insert ID within a transaction in Yii using DAO?

Question

That's the source code, I need to detect the ID (see the marked position between the two queries below).

$connection = Yii::app()->db;
$transaction=$connection->beginTransaction();
try {

    $q = "INSERT INTO `someTable1` .... ";      
    $connection->createCommand($q)->execute(); // Single Row Inserted

    // HERE!! How to get the last insert ID from query above

    $q = "INSERT INTO `someTable2` ....
          WHERE id = LAST_INSERT_ID_FROM_FIRST_QUERY ";
    $connection->createCommand($q)->execute();

    $transaction->commit();

} catch (Exception $e) {
    // react on exception   
    $trans->rollback();
} 

What would be the most suitable way to do that?

Rollicking

Have you tried using getLastInsertID() from CDbConnection class? (yiiframework.com/doc/api/1.1/…) – Flog

Answer 1

$lastInsertID = $connection->getLastInsertID();

Rosalba

this doesnt work when using after batch insert because batch insert works like stack and even if you getLastInsertID it returns the records which is actually the first new record in database – Outfox

but this is an another use case – Rosalba

Answer 2

you can try both way,here getLastInsertID is method and lastInsertID is property

$lastInsertID = $connection->getLastInsertID();

or

$lastInsertID = $connection->lastInsertID;

for more info http://www.yiiframework.com/doc/api/1.1/CDbConnection

Inning

Answer 3

i created this to solve that issue

public static function getAutoIncrement($table_name)
{
    $q = new Query();
    $res = $q->select("AUTO_INCREMENT")
        ->from('INFORMATION_SCHEMA.TABLES')
        ->where("TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" . $table_name . "'")
        ->one();
    if ($res)
        return $res["AUTO_INCREMENT"];
    return false;
}

Waugh

Answer 4

For anyone yet interested:

ActiveRecord in saveMethod does this like

\Yii::$app->db->schema->insert($tableName, $values)

It results like

["id" => 1]

Schema in it's way does this:

public function insert($table, $columns)
{
    $command = $this->db->createCommand()->insert($table, $columns);
    if (!$command->execute()) {
        return false;
    }
    $tableSchema = $this->getTableSchema($table);
    $result = [];
    foreach ($tableSchema->primaryKey as $name) {
        if ($tableSchema->columns[$name]->autoIncrement) {
            $result[$name] = $this->getLastInsertID($tableSchema->sequenceName);
            break;
        }

        $result[$name] = isset($columns[$name]) ? $columns[$name] : $tableSchema->columns[$name]->defaultValue;
    }

    return $result;
}

I suggest rather use schema->insert. It supports composite identifiers and uses sequence name for fetching last ID

Robinson