Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I'm trying to run a simple conditional Insert, but the query keeps on adding the row to the table even though the condition should not allow it. Can you please check if am I missing anything? Thanks
laravel 11, no errors.
DB::table('my_table')
->wherenotExists(function (Builder $query) {
$query->select('id', 'fname')
->from('my_table')
->whereColumn('id', '57');
// I also tried: ->where('id', '57') ;
// id is the primary key, and id=57 exists.
})
->insert([ // this is always executed
'fname' => 'spiderman4',
'uid' => 4
]);
The following also keeps on inserting the row:
DB::table('my_table')
->where('id', '57')
->havingRaw('count(*) =0')
->insert([
'fname' => 'spiderman4',
'uid' => 4
]);
https://laravel.com/docs/11.x/queries https://laracasts.com/discuss/channels/eloquent/conditional-insert
@alex32 Based on my understanding, whereNotExists does not work the way you expect!.
To achieve your expectations, I think you can try firstOrCreate() method. For example:
DB::table('my_table')->firstOrCreate(
['id' => 57],
['fname' => 'spiderman4', 'uid' => 4]
);
https://laravel.com/docs/11.x/eloquent#retrieving-or-creating-models
NOTE: Don't forget to adjust your value and columns.
@tisuchi Thanks for your reply. But firstOrCreate() is an Eloquent method, doesn't work in QBuilder.
Call to undefined method Illuminate\Database\Query\Builder::firstOrCreate()
I thought QBuilder had a way to accomplish that. But havingRaw() doesn't seems to work either. I'll keep 2 queries. Thanks
@alex32 Then use Eloquent.
MyTable::firstOrCreate(
['id' => 57],
['fname' => 'spiderman4', 'uid' => 4]
);
Yes I know, but I need to create a model for each table which is an overhead sometimes. And I suppose Elequent firstOrCreate() will translate in 2 queries anyway, so I'm keeping my 2 queries in QBuilder. Thanks
@alex32, just to clarify, do you want to insert only if a condition is met?
And otherwise skip the insert? On a single SQL statement?
The query builder insert method will always try to convert the query to an insert. And as all drivers have the usual insert coded, where statements are ignored.
As you already know, Eloquent's firstOrCreate will execute two queries (one SELECT and one INSERT if needed), I don't believe you can avoid having two queries in case you want to insert something matching a criterion.
MySQL supports INSERT IGNORE, but that would only apply for unique constraints (including primary keys). If that is your case (avoiding inserting a record with an existing primary key), then you could write the raw SQL statement and use DB::statement() to run it.
If all you want is to avoid writing an if clause, you can use the DB::existsOr() method from the query builder:
DB::table('my_table')
->where('id', 57)
->existsOr(fn () => DB::table('my_table')->insert([/*...*/]));
Which internally will run a select to check existence, and will run the callback only if it doesn't exist.
There is a companion DB::doesntExistOr() method, in case I swapped your insert logic.
Just for completeness, MySQL supports INSERT...SELECT which will insert records returned from a SELECT. Others DBMS have similar constructs.
That would do, as if no records are returned from the SELECT, no records are inserted.
But I don't believe Laravel's Query Builder supports building this statement.
Reference: https://dev.mysql.com/doc/refman/8.0/en/insert-select.html
@rodrigo.pedra Thanks for your help. Yes, my intention was to avoid writing 2 queries, less code, less bugs. I'll give a try to existsOr() Many thanks
Please or to participate in this conversation.