Check if name is unique among non-deleted items with laravel validation

Question

I have a simple form which posts to a controller which checks if a name for an item is already taken for a particular project. If it is, then it returns an error. This is the code I'm using for that:

'name'    => 'required|min:1|unique:versions,name,NULL,id,project_id,'.$project->id,

The problem I've run into is that instead of a hard delete, I'm using a soft delete to remove them from the database, meaning that, for example, 'Test' can only be used as the name once, even after it's been deleted.

How can I make it check that it is unique for that project among the items that are not soft deleted?

Solution 1

You may try this:

'name' => 'required|min:1|unique:versions,name,NULL,id,deleted_at,NULL'

This will make sure that the name in the versions table will be unique, if a record is soft deleted and has same name name then it won't be counted, means, name will be accepted even if there is a soft deleted record with the same name exists.

To ignore a model when updating, you should pass the id after name in the place of first NULL.

Update: Also you may use something like this to add your own custom rule:

// You can declare it inside your controller method before you run validation
Validator::extend('unique_project', function($attribute, $value, $parameters)
{
   // $attribute will contain field name, i.e. name
   // $value will contain the value in the $attribute/name
   // $parameters will be an array of arguments passed
   // i.e. [0] => arg1, [1] => arg2, [2] => arg3 and so on

   return true for valid and false for invalid

});

You may use it like this:

'name' => 'required|min:1|unique_project:arg1,arg2,arg3' // add more args if needed

Solution 2

I know this question is old, but I stumbled across this when looking for a solution to a similar problem. You don't need custom validation code.

I have a database of ledger codes in which the 'name' and 'short_name' must be unique for each user (user_id). I have soft deletes enabled, so they should only be unique among non-deleted rows for a given user.

This is my function which returns the validation strings:

protected function validation_data($user_id, $update_id = "NULL") {
        return [
        'name' => "required|max:255|unique:ledger_codes,name,$update_id,id,deleted_at,NULL,user_id,$user_id",
        'short_name' => "max:255|min:3|unique:ledger_codes,short_name,$update_id,id,deleted_at,NULL,user_id,$user_id",
        'description' => 'max:255',
        ];
    }

For any one wondering about the argument string syntax for the unique validator, it is as follows:

• arg 0: The table name in the database
• arg 1: the field name in which the value is unique
• arg 2: a single id which is to be ignored (set to uppercase NULL if you are not using it.)
• arg 3: the field in which the single id resides. It defaults to 'id' so if you are not using it, and you have more arguments, use the string 'id'.
• arg 4/5: a field name/value pair for a where clause (where('deleted_at',null) in my example.)
• arg 6/7: another field name/value pair (where('user_id', $user_id) in my example).
• arg 8/9: another field name value pair
• arg 10/11: another.......
  ... and so on.

The value fields in field name/value pairs can be either a value to match, NULL, or NOT_NULL.

Solution 3

If someone is looking for solution using Rule class.

use Illuminate\Validation\Rule;

class UpdateArticleRequest extends FormRequest
{
    /**
     * Determine if the user is authorized to make this request.
     *
     * @return bool
     */
    public function authorize()
    {
        return true;
    }

    /**
     * Get the validation rules that apply to the request.
     *
     * @return array
     */
    public function rules()
    {
        $data = $this->request->all();
        
        return [
            'slug' => [
                'required',                
                Rule::unique('articles')->ignore($data['id'])->whereNull('deleted_at')
            ]
        ];
    }
}

Basically, we just ignoring rows which deleted_at fields are not null.

Here are the methods which you can use along with ignore function: https://laravel.com/api/5.8/Illuminate/Validation/Rules/DatabaseRule.html

Solution 4

For add record

'name' => [
                'required',
                'string',
                'min:3',
                Rule::unique('products')->where(function ($query) {
                    return $query->where('store_id', Auth::user()->store_id);
                })->whereNull('deleted_at'),                
            ],

For edit that record

'name' => [
                'required',
                'string',
                'min:3',
                Rule::unique('products')->where(function ($query) {
                    return $query->where('store_id', Auth::user()->store_id);
                })->ignore($request->id, 'id')->whereNull('deleted_at'),                
            ],

Solution 5

On Create Method:

public function store(Request $request)
{

    $request->validate([

        'name'=>'required|unique:form_types,name,NULL,id,deleted_at,NULL',

    ]);

    // Write your code here

}

On Update Method:

public function update(Request $request, $id)
{

    $request->validate([

        'name'=>'required|unique:form_types,name,'.$id.',id,deleted_at,NULL',

    ]);

    // Write Code here

}

View more solutions

Comments

[Samsquanch]

Thanks, but there's a problem with this -- it doesn't take the project_id into account. The uniqueness is on a per-project basis. So, project 1 can have a version named 'Test' and project 2 can also have a version named 'Test'. The validation you posted would not allow project 2 to have 'Test', only project 1.

[The Alpha]

You can add the project_1 id to ignore that even when uou are inserting, add pass the id after name in the place of NULL.

[Samsquanch]

Unfortunately that won't work either. I need to only look at the versions with the same project id, not projects that don't have the same ID.

[The Alpha]

Sorry! Your now confusing me. maybe you need to rephrase your question, you didn't mentioned about this in your question earlier.

[Samsquanch]

In the first sentence: "which checks if a name for an item is already taken for a particular project" and also "How can I make it check that it is unique for that project". The question is looking for an extension to or alteration of the code I posted to meet further requirements.

[The Alpha]

You can use extend to add a custom rule so you'll get more freedom of code.

[Samsquanch]

I was trying to avoid writing something custom if what I needed was built it, but I'll still throw you an upvote for effort.

[The Alpha]

Appreciated your gift :-)

[user151496]

i love this answer

[Sachin Kumar]

Yes, this answer does not include the complexity because the abstraction sometimes makes things complicated and in this question case, we have to understand the position of each parameter for single validation logic. fuss.

[coderdonezo]

I can't figure out how to write this if I want deleted_at != 0, any idea ?