Feature Request: Implement firstOrInsert for Query Builder - “Find or Create” Logic

[mohammadrasoulasghari]

Hello Laravel team and community,

Currently, the Laravel Query Builder offers the updateOrInsert method, which is incredibly useful for ensuring records exist and updating them if necessary. However, there is no direct equivalent for a ‘find or create’ operation, like Eloquent’s firstOrCreate.

This functionality is particularly useful in scenarios where we need to ensure a record exists in the database, but don’t want to update it if it already does. A prime example is in migrations and seeders, where we often need to create default or initial data. In these contexts, using Eloquent models might be considered less ideal or unnecessary for simple record creation at the database level.

Proposed Solution:

If there is consensus on the usefulness of this feature, I would be happy to prepare a Pull Request with a proposed implementation of firstOrInsert for the Query Builder.
. This method would behave as follows:1. Attempt to find the first record matching the given where clauses.
2. If a record is found, return that record.
3. If no record is found, insert a new record with the provided values (along with the where clauses) and return the newly created record.

Use Cases:

Migrations: Ensuring default values or initial records exist for certain tables. For example, creating a default ‘admin’ user role if it doesn’t exist:

Schema::create('roles', function (Blueprint $table) { $table->id(); $table->string('name')->unique(); // ... other columns });

// Migration's up() method:
DB::table('roles')->firstOrInsert( ['name' => 'admin'], ['name' => 'admin', /* ... other default values */] // Values to insert if not found );
Current Workaround & Why firstOrInsert is Better:

Currently, to achieve ‘first or insert’ logic with the Query Builder, we need to perform two separate queries: first() to check existence, and insert() if needed. This approach is less efficient (two queries instead of potentially one in some database systems) and less readable.

A dedicated firstOrInsert method would streamline this common pattern, making the code cleaner and potentially more performant.

Consistency with updateOrInsert:

The Query Builder already provides updateOrInsert. Adding firstOrInsert would create a more consistent and complete API, offering both ‘find-or-modify’ and ‘find-or-create’ operations directly at the query level.

Call for Feedback:

I believe that adding firstOrInsert to the Query Builder would be a valuable addition, enhancing its usability and reducing boilerplate code in common scenarios. I would love to hear your thoughts on this proposal and whether it aligns with the direction of Laravel.

If there is consensus on the usefulness of this feature, I would be happy to prepare a Pull Request with a proposed implementation.

Thank you for your time and consideration.

[ghost]

Hi. Nothing stops you from creating a macro for this.

[mohammadrasoulasghari]

Yes, that's right, but considering its use and existence, doesn't it seem better to add Eloquent to the builder itself by default?

[ghost]

I did not encounter any use case for it because you would need to send all data needed for insert in a get call.

There is a lib that implemented upsert via api in the same manner https://github.com/macropay-solutions/laravel-crud-wizard-free/blob/7d1e1d24e257f51b4249a7ca4d0e09f097bd0d7f/src/Http/Controllers/ResourceControllerTrait.php#L166

but also there you have to send in body all fields for create if update fails-get by id does not find the resource.

In your case you are using a GET not a PUT if you are using REST api calls, so sending data as query param would not be recommended.

If you are not using this in an api context, then you still have to send to this function all fields needed (validated) for create.

Bottom line is that if I need to create if not found I would just do a try catch and do it in plain site without hiding this in the laravel code.

[ghost]

Sorry . My above comment is for find not first. First is even broader than find.

[mohammadrasoulasghari]

So do you think it's good to have it in the query builder?

[ghost]

I think it exceeds its purpose because you have to send it the create fields that will not be used if found. Those fields need to be valid ones and for me personally, it is overhead.
Also because it is first (not unique) not find (unique) it might generate duplicate sql error.

You can do a PR with the change and you will see the feedback you get.