CODEWITHSUNDEEP
phplaravellaravel-validation
user13992078
user13992078

Reputation:

How can I enforce the uniqueness of a record in Laravel's database based on another column?

I have a Laravel 8.x application with a database table that includes columns: blockID (primary key), blockName, createdBy, and delete (boolean).

When a user adds a new block and submits the form, I want to validate that the blockName is unique. If it is unique, the blockName is stored in the database with the delete column set to false. When the user clicks the delete button, the corresponding row's delete column is updated to true. Although the data remains in the database (not permanently deleted), it should not be displayed on the web page.

Here is the validation code I'm currently using:

$validatedValues = $request->validate([
    'blockName' => 'required|unique:blocks',
]);

For deletion, this is the code I have implemented:

public function destroy($id)
{
    $block = Block::find($id);
    $block->delete = true;

    if ($block->update()) {
        return response()->json(['success' => true]);
    }
}

My question is: How can I ensure that when Laravel validates the blockName as unique, it excludes records where the delete column has been set to true? I would greatly appreciate any insights or suggestions.

Thank you for your assistance!

Regards.

Upvotes: 0

Views: 888

Answers (3)

zjbarg
zjbarg

Reputation: 679

You need to do something like this:

Rule::unique('blocks', 'blockName')->where(fn ($qry) => $qry->where('delete', 0));

Upvotes: 0

Peppermintology
Peppermintology

Reputation: 10210

You could create a custom validation rule to contain your logic.

php artisan make:rule UniqueOrDeleted

App\Rules\UniqueOrDeleted.php

<?php

namespace App\Rules;

use Illuminate\Contracts\Validation\Rule;

class UniqueOrDeleted implements Rule
{
    /**
     * The model we're going to be performing the query against
     *
     * @var string
     */
    private $model;

    /**
     * Create a new rule instance.
     *
     * @param string $model
     *
     * @return void
     */
    public function __construct(string $model)
    {
        $this->model = $model;
    }

    /**
     * Determine if the validation rule passes.
     *
     * @param  string  $attribute
     * @param  mixed  $value
     * @return bool
     */
    public function passes($attribute, $value)
    {
        return $this->model::query()
                ->where($attribute, $value)
                ->where('delete', true)
                ->count();
    }

    /**
     * Get the validation error message.
     *
     * @return string
     */
    public function message()
    {
        return 'The validation error message.';
    }
}

Then you can use it as follows.

$validatedValues = $request->validate([
    'blockName' => ['required', new UniqueOrDeleted(Block::class)],
]);

We provide a model as a constructor argument just in case you want to use the same concept elsewhere and means you don't have to hard code table names or anything. You could take that concept further and provide a second argument which woud specify the name of the delete field in your table.

Upvotes: 0

Jibin Bose
Jibin Bose

Reputation: 589

If you haven't set the unique condition on the blockName column of your underlying database table. Then you could do something like the below to solve your problem.

$request->validate([
  'blockName' => 'required|unique:blocks,delete,FALSE'
]);

Note: I am not quite sure if this how you pass boolean values to validator. The below code will work perfectly if using soft deletes.

I would suggest you implement the SoftDeletes trait of the laravel models to make your life simpler. This will add a deleted_at column to your table. Laravel will automatically omit rows that have deleted_at in it for you. Calling the normal delete method of your model will only set the deleted_at field and won't remove it from DB also. This will remove the burden to update delete column from your end.

If you choose to do so. The validation is like below.

$request->validate([
  'blockName' => 'required|unique:blocks,deleted_at,NULL'
])

Upvotes: 0

Related Questions