By garethsprice on

When using db_merge, it does not seem to be possible to retrieve the last insert ID if an INSERT was made.

MergeQuery::execute() returns MergeQuery::STATUS_INSERT if an insert was made, not the insert ID as InsertQuery::execute() does.

Is there an elegant way to retrieve the last insert ID when using db_merge?

Sources: http://api.drupal.org/api/drupal/includes--database--query.inc/function/... http://api.drupal.org/api/drupal/includes--database--query.inc/function/...

⋅ Categories: Drupal 7.x

Comments

Bagz’s picture

Bagz commented

The last insert id would be the highest id, so you could use
SELECT id FROM mytable ORDER BY id DESC LIMIT 1

You'd have to retrieve the highest id before the merge if you wanted to determine if any insert(s) were made at all..

garethsprice’s picture

garethsprice commented

That introduces a race condition where there's a chance (however slim) that another record may be inserted before you have a chance to get to it.

I found a solution in modules/simpletest/tests/database_test.test where we requery the database using known values to retrieve the record and fetch it's ID. Still seems odd there's no easier way to get the last insert ID, but this works for my purposes.

    $result = db_merge('test_people')
      ->key(array('job' => 'Presenter'))
      ->fields(array(
        'age' => 31,
        'name' => 'Tiffany',
      ))
      ->execute();
    $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Presenter'))->fetch();
    $last_insert_id = $person->id; // Core test does not actually include an ID, but this is how it could work
dalin’s picture

dalin
he/him
Primary language English
Location 🇨🇦, 🇲🇽, 🌍
commented

This does not fix the race condition. Instead you can use:

Database::getConnection()->lastInsertId();

Will always return the lastinsert ID for the current connection (regardless of what else is simultaneously acting on the DB).

________________________
Dave Hansen-Lange
Director of Technical Strategy, FourKitchens.com

roblog’s picture

roblog
Primary language English
Location Cymru
commented

Cheers @dalin useful tip! This only half works though, because if it is an update instead of an insert, then it does not return the value.

Dirst’s picture

Dirst commented

No it is pretty enough. On Update operation you already know the ID that has been passed to perform update.