L8 - Postgres - 'ON CONFLICT DO NOTHING RETURNING id' statement

Hi all

I need to submit a query like:

insert into mytable (field1, field2) values (value1, value2) ON CONFLICT DO NOTHING RETURNING id

I tried to use insertOrIgnore() method but It build the query with only ON CONFLICT DO NOTHING; I tried to use insertGetId() method It build the query with only RETURNING id.

Is there a way to "chain" the two methods?

I also tried to build a raw insert:

DB::insert('insert into mytable (field1, field2) values (?, ?) ON CONFLICT DO NOTHING returning "id"', [
                $value1,
                $value2
            ]);

but It doesn't return the id; It returns only true or false.

Can someone help me?

Sinnbeck

4 years ago

Level 102

How about this?

DB::select('insert into mytable (field1, field2) values (?, ?) ON CONFLICT DO NOTHING returning "id"', [
                $value1,
                $value2
            ]);

vlauciani

4 years ago

Level 1

I tried your solution after submit this post and It seems to work! Thank you...

...but I would like to use Eloquent to ensure that It works on all DB (MySQL, Postgres, ecc...); the best solution should be to use the chain of insertOrIgnore() and insertGetId().

MohamedTammam

4 years ago

Level 51

@vlauciani That sounds great, please mark the post as solved.

vlauciani

4 years ago

Best Answer

Level 1

This is the final solution using RAW query instead of Eloquent.

$myQuery = 'insert into mytable (field1, field2) values (value1, value2) ON CONFLICT DO NOTHING returning id; ';
$output = DB::select($myQuery);
if (!empty($output)) { // the record was inserted; get id
    $pickId = $output[0]->id;
} else { // the record already exists; get id
    $output = Mytable::where([
        ['field1', '=', 'value1'],
        ['field2', '=', 'value2'],
    ])->first();
    $pickId = $output->id();
}

1 like

Please or to participate in this conversation.