Insert On Duplicate Key Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require yadakhov/insert-on-duplicate-key
   

   Add the trait to your Eloquent model:

   use Yadakhov\InsertOnDuplicateKey\InsertOnDuplicateKey;
   
   class User extends Model
   {
       use InsertOnDuplicateKey;
   }
   

2. First Use Case Define a unique key (e.g., email) in your migration:

   $table->string('email')->unique();
   

   Use insertOnDuplicateKey in your model:

   $user = User::insertOnDuplicateKey([
       'email' => 'test@example.com',
       'name'  => 'John Doe',
   ], [
       'name' => 'John Updated', // Fields to update on duplicate
   ]);
   

Implementation Patterns

Workflows

1. Bulk Inserts with Upserts

   $users = [
       ['email' => 'a@example.com', 'name' => 'Alice'],
       ['email' => 'b@example.com', 'name' => 'Bob'],
   ];
   User::insertOnDuplicateKey($users, ['name' => 'Updated']);
   

2. Conditional Updates Use closures for dynamic updates:

   $user = User::insertOnDuplicateKey([
       'email' => 'test@example.com',
       'active' => false,
   ], function ($attributes) {
       return ['active' => true]; // Only update if duplicate
   });
   

3. Integration with Queues Queue jobs for delayed upserts:

   User::dispatchSyncOnDuplicateKey([
       'email' => 'test@example.com',
       'name'  => 'John',
   ], ['name' => 'John']);
   

Integration Tips

• Laravel Collections: Chain with collections for batch processing:

  collect($users)->each(function ($user) {
      User::insertOnDuplicateKey($user, ['last_updated' => now()]);
  });
  

• API Responses: Return the upserted/updated record:

  return response()->json(
      User::insertOnDuplicateKey($data, $updates)
  );
  

Gotchas and Tips

Pitfalls

1. Unique Key Mismatch

   • Ensure the ON DUPLICATE KEY UPDATE fields match the unique/primary key constraints.
   • Debug with raw SQL:

     \DB::enableQueryLog();
     User::insertOnDuplicateKey(...);
     dd(\DB::getQueryLog());
     

2. Closure Evaluation

   • Closures are evaluated only on duplicates. Test edge cases where no duplicate exists.

3. Mass Assignment

   • Use $fillable or $guarded to prevent unintended updates.

Debugging

• Silent Failures: Wrap in try-catch for validation errors:

  try {
      User::insertOnDuplicateKey($data, $updates);
  } catch (\Illuminate\Database\QueryException $e) {
      // Handle validation errors
  }
  

Extension Points

1. Custom Query Builder Extend the trait for raw queries:

   use Yadakhov\InsertOnDuplicateKey\InsertOnDuplicateKey as BaseTrait;
   
   class CustomInsertOnDuplicateKey extends BaseTrait {
       public function customUpsert($data, $updates) {
           return \DB::table('users')->insertOnDuplicateKey($data, $updates);
       }
   }
   

2. Event Hooks Listen for eloquent.saving to modify updates dynamically:

   User::saved(function ($model) {
       if ($model->wasRecentlyCreated) {
           // Post-upsert logic
       }
   });
   

3. Soft Deletes Override getKeyName() if using soft deletes:

   protected $primaryKey = 'id';
   public function getKeyName() { return 'deleted_at'; } // Custom logic