Laravel unique validation on multiple columns

Question

I have 2 columns in table servers.

I have columns ip and hostname.

I have validation:

'data.ip' => ['required', 'unique:servers,ip,'.$this->id]

This working only for column ip. But how to do that it would work and for column hostname?

I want validate data.ip with columns ip and hostname. Because can be duplicates in columns ip and hostname, when user write ip.

Answer 1

You can use Rule::unique to achieve your validation rule

$messages = [
    'data.ip.unique' => 'Given ip and hostname are not unique',
];

Validator::make($data, [
    'data.ip' => [
        'required',
        Rule::unique('servers')->where(function ($query) use($ip,$hostname) {
            return $query->where('ip', $ip)
            ->where('hostname', $hostname);
        }),
    ],
],
$messages
);

edit: Fixed message assignation

Answer 2

The following will work on the create

'data.ip' => ['required', 'unique:servers,ip,'.$this->id.',NULL,id,hostname,'.$request->input('hostname')]

and the following for the update

'data.ip' => ['required', 'unique:servers,ip,'.$this->id.','.$request->input('id').',id,hostname,'.$request->input('hostname')]

I'm presuming that id is your primary key in the table. Substitute it for your environment.

---

The (undocumented) format for the unique rule is:

table[,column[,ignore value[,ignore column[,where column,where value]...]]]

Multiple "where" conditions can be specified, but only equality can be checked. A closure (as in the accepted answer) is needed for any other comparisons.

Answer 3

Table

server

Field

• id primary key

• ip should be unique with hostname

• hostname should be unique with ip

Here I validate for Ip and the hostname should be unique.

use Illuminate\Validation\Rule;

$ip = '192.168.0.1';
$host = 'localhost';

While Create

Validator::make($data, [
    'ip' => [
        'required',
         Rule::unique('server')->where(function ($query) use($ip,$host) {
           return $query->where('ip', $ip)->where('hostname', $host);
         });
    ],
]);

While Update

Add ignore after RULE

Validator::make($data, [
    'ip' => [
        'required',
         Rule::unique('server')->where(function ($query) use($ip,$host) {
           return $query->where('ip', $ip)->where('hostname', $host);
         })->ignore($serverid);
    ],
]);

Answer 4

Laravel 5.6 and above

Validation in the controller

The primary key (in my case) is a combination of two columns (name, guard_name)

I validate their uniqueness by using the Rule class both on create and on update method of my controller (PermissionsController)

---

PermissionsController.php

<?php

namespace App\Http\Controllers;

use App\Permission;

use Illuminate\Http\Request;
use Illuminate\Validation\Rule;
use App\Http\Controllers\Controller;

class PermissionsController extends Controller
{

    /**
     * Store a newly created resource in storage.
     */
    public function store(Request $request)
    {
        request()->validate([

            'name'        => 'required|max:255',

            'guard_name'  => [

                'required', 

                Rule::unique('permissions')->where(function ($query) use ($request) {

                    return $query
                        ->whereName($request->name)
                        ->whereGuardName($request->guard_name);
                }),
            ],
        ],
        [
            'guard_name.unique' => __('messages.permission.error.unique', [

                'name'              => $request->name, 
                'guard_name'        => $request->guard_name
            ]),
        ]);

        Permission::create($request->all());

        flash(__('messages.permission.flash.created'))->success();

        return redirect()->route('permission.index');
    }


    /**
     * Update the specified resource in storage.
     */
    public function update(Request $request, Permission $permission)
    {
        request()->validate([

            'name'        => 'required|max:255',

            'guard_name'  => [

                'required', 

                Rule::unique('permissions')->where(function ($query) use ($request, $permission) {

                    return $query
                        ->whereName($request->name)
                        ->whereGuardName($request->guard_name)
                        ->whereNotIn('id', [$permission->id]);
                }),
            ],
        ],
        [
            'guard_name.unique' => __('messages.permission.error.unique', [

                'name'              => $request->name, 
                'guard_name'        => $request->guard_name
            ]),
        ]);

        $permission->update($request->all());

        flash(__('messages.permission.flash.updated'))->success();

        return redirect()->route('permission.index');
    }
}

Notice in the update method i added an additional query constraint [ whereNotIn('id', [$permission->id]) ] to ignore the current model.

---

resources/lang/en/messages.php

<?php

return [

    'permission' => [

        'error' => [
            'unique' => 'The combination [":name", ":guard_name"] already exists',
        ],

        'flash' => [
            'updated' => '...',
            'created' => '...',
        ],
    ]
]

---

The flash() method is from the laracasts/flash package.

Answer 5

This works for me for both create and update.

[
     'column_1' => 'required|unique:TableName,column_1,' . $this->id . ',id,colum_2,' . $this->column_2
]

Note: tested in Laravel 6.

Answer 6

With Form Requests:

In StoreServerRequest (for Create)

public function rules() {
    'ip' => [
        'required',
         Rule::unique('server')->where(function ($query) {
             $query->where('ip', $this->ip)
                ->where('hostname', $this->host);
         })
    ],
}

public function messages() {
    return [
       'ip.unique' => 'Combination of IP & Hostname is not unique',
    ];
}

In UpdateServerRequest (for Update)

Just Add ignore at the end

public function rules() {
    'ip' => [
        'required',
         Rule::unique('server')->where(function ($query) {
             $query->where('ip', $this->ip)
                ->where('hostname', $this->host);
         })->ignore($this->server->id)
    ],
}

Answer 7

Try this rule:
'data.ip' => 'required|unique:servers,ip,'.$this->id.'|unique:servers,hostname,'.$this->id

Answer 8

This is the demo code. It would help you much better. I tried covering both insert and update scenarios.

Inside app/Http/Providers/AppServiceProvider.php

Validator::extend('uniqueOfMultiple', function ($attribute, $value, $parameters, $validator)
    {
        $whereData = [
            [$attribute, $value]
        ];

        foreach ($parameters as $key => $parameter) {

            //At 0th index, we have table name
            if(!$key) continue;

            $arr = explode('-', $parameter);

            if($arr[0] == 'except') {
                $column = $arr[1];
                $data = $arr[2];

                $whereData[] = [$column, '<>', $data];
            } else {
                $column = $arr[0];
                $data = $arr[1];

                $whereData[] = [$column, $data];
            }
        }

        $count = DB::table($parameters[0])->where($whereData)->count();
        return $count === 0;
    });

Inside app/Http/Requests/Something/StoreSometing.php

/**
 * Get the validation rules that apply to the request.
 *
 * @return array
 */
public function rules()
{
    return [
        'name' => 'required|max:225|uniqueOfMultiple:menus,location_id-' . $this->get('location_id', 'NULL') . ',language_id-' . $this->get('language_id', 1),
        'location_id' => 'required|exists:menu_location,id',
        'order' => 'digits_between:0,10'
    ];
}

Inside app/Http/Requests/Something/UpdateSomething.php

/**
 * Get the validation rules that apply to the request.
 *
 * @return array
 */
public function rules()
{
    return [
        'name' => 'required|max:225|uniqueOfMultiple:menus,location_id-' . $this->get('location_id', 'NULL') . ',language_id-' . $this->get('language_id', 'NULL') . ',except-id-' . $this->route('id', 'NULL'),
        'location_id' => 'required|exists:menu_location,id',
        'order' => 'digits_between:0,10'
    ];
}

Inside resources/lang/en/validation.php

'unique_of_multiple' => 'The :attribute has already been taken under it\'s parent.',

Here in this code, the custom validation used is uniqueOfMultiple. The first argument passed is the table_name i.e menus and all other arguments are column_name and are comma-separated. The columns are used here, name (primary column), location_id, language_id and one except-for column for the update case, except-id. The value passed for all three is - separated.

Answer 9

This works for me for both create and update.

in your ServerUpdateRequest or ServerCreateRequest class

public function rules()
{            
    return [
       'column_1' => 'required|unique:TableName,column_1,' . $this->id . ',id,colum_2,' . $this->column_2 . ',colum_3,' . $this->column_3,
    ];
}

This command run background a aggregate Sql like this

select
   count(*) as aggregate 
from 
  `TableName` 
where 
  `column_1` = <postedColumn1Value>  
   and `id` <> idValue 
   and `column_2` = <postedColumn2Value> 
   and `column_3` = <postedColumn3Value> 

tested in Laravel 9. and it works

Note: if you want to see background sql for debugging (For example, to check if the request values are empty[$this->]) , especially you have to write wrong code, For example, you may enter a filed name incorrectly.

Answer 10

The following code worked nicely for me at Laravel 8

Create:

'required|unique:TableName,column_1,' . $this->column_1 . ',id,colum_2,' . $this->column_2,

Example:

public function store(Request $request)
{
    $union = auth()->user()->union_id;
    $request->validate([
        'holding_no' => 'required|integer|unique:holding_taxes,holding_no,' . $request->holding_no . ',id,union_id,' . $union,
    ]);   
}

Update:

'required|unique:TableName,column_1,' . $this->id . ',id,colum_2,' . $this->column_2,

Example:

public function update(Request $request, $id)
{
    $union = auth()->user()->union_id;
    $request->validate([
        'holding_no' => 'required|unique:holding_taxes,holding_no,' . $id . ',id,union_id,'.$union,
    ]);   
}

Answer 11

for me laravel 8 this works

$req->validate([
    'house_no' => [
        Rule::unique('house')
          ->where('house_no', $req->input('house_no'))
          ->where('ward_no', $req->input('ward_no'))
    ],
]);

Answer 12

Simple solution with call back query

  Rule::unique('users')->where(fn ($query) => $query->where(['project_id'=> request()->project_id, 'code'=> request()->code ])),

Answer 13

This code works in Laravel 9 to make two columns unique together

Create Request

public function rules()
    {
        return [
                'column1' => [
                'string',
                'required',
                Rule::unique('table_name')
                     ->where('column2', $this->column2)
                ],
            ];
    }

Answer 14

public function store(Request $request)
    {
         $this->validate($request, [
            'first_name' => 'required|regex:/^[\pL\s\-]+$/u|max:255|unique:contacts,first_name, NULL,id,first_name,'.$request->input('last_name','id'),
            'last_name'=>'required|regex:/^[\pL\s\-]+$/u|max:255|unique:contacts,last_name',
            'email' => 'required|email|max:255|unique:contacts,email',
            'job_title'=>'required',
            'city'=>'required',
            'country'=>'required'],
            [
             'first_name.regex'=>'Use Alphabets Only',
             'email.unique'=>'Email is Already Taken.Use Another Email',
             'last_name.unique'=>'Contact Already Exist!. Try Again.',
            ]
        );