Make Column Unique Based on Foreign Key in Laravel

user_6883910 0
Viewed 777

I have two models EventLocation and EventDepartment. The models and relationship are shown below:

class EventLocation extends Model
{
    public function event_departments()
    {
        return $this->hasMany(EventDepartment::class);
    }

}

class EventDepartment extends Model
{
    public function event_location()
    {
        return $this->belongsTo(EventLocation::class, 'location_id');
    }
}

The migration for the EventDepartment contains the location_id as a foreign key as shown:

Schema::create('event_departments', function (Blueprint $table) {
      $table->id();

      $table->string('name');
      $table->foreignId('location_id')->constrained('event_locations');

      $table->softDeletes();
      $table->timestamps();
});

The issue I am facing now is that, how do I make a column unique in the EventDepartment table when creating a new deparment based on the foreign key which is location_id?

i.e An EventDepartment cannot have columns with the same name if they belong to the same EventLocation but i'm able create another EventDepartment with an existing name as long as it belongs to a different EventLocation

I have tried with this but isn't working:

public function store(Request $request)
    {
        $this->validate($request, [
            'name' => 'required|min:3|unique:event_departments,location_id',
            'location_id' => 'required',
        ]);

        $department = EventDepartment::create([
            'name' => $request->name,
            'location_id' => $request->location_id,
        ]);

        return new EventDepartmentResource($department);
    }
eloquent laravel
2 Answers

While creating migration you should set uniqueness by related fields. In your example it should be by name and location_id

public function up()
{
    Schema::create('event_departments', function (Blueprint $table) {
        $table->id();

        $table->string('name');
        $table->foreignId('location_id')->constrained('event_locations');

        $table->softDeletes();
        $table->timestamps();

        $table->unique(['name', 'location_id'], 'unique_name_location');
    });
}

public function down()
{
    Schema::enableForeignKeyConstraints();
    Schema::table('event_departments', function (Blueprint $table) {
        $table->dropForeign(['location_id']);
        $table->dropUnique('unique_name_location');
    })
    Schema::disableForeignKeyConstraints();

    Schema::drop('event_departments');
}

This will ensure DB level, table's established rules and it is there will not be more than one same name with same location_id. Docs.

On PHP level, you have to write your own rule class that will fire and check against rule that DB can accept.

-Make a rule class

php artisan make:rule UniqueNameLocationRule

-Rule class code

public function __construct(string $name, int $locationId)
{
    $this->name = $name;
    $this->locationId = $locationId;
}

public function passes($attribute, $value)
{
    return !EventDepartment::where([
        'name' => $this->name,
        'location_id' => $this->locationId,
    ])->exists();
}

-Validation code

'name' => ['bail', 'required', 'min:3', new UniqueNameLocationRule((string)$request->name, (int)$request->location_id)],
'location_id' => ['required', 'exists:event_locations,id'],

Docs.

This should work, please test and tell if there's some error.

user_9396300

I think,in your validation, your uniqiue property is in wrong place. You are checking that 'is "name" variable unique at event_departments table in location_id column' in your code but you don't want to check name. You want to check location_id. If i get your question wrong,I'm sorry.

$this->validate($request, [
        'name' => 'required|min:3',
        'location_id' => 'required|unique:event_departments,location_id',
    ]);

Can you try this? Or if you want to check name,try this one;

$this->validate($request, [
        'name' => 'required|min:3|unique:event_departments,name',
        'location_id' => 'required',
    ]);
user_135113130
Related