Sometimes you need to ensure a record is unique, but only if another has a certain value. Does this sound strange? Let me give you an example:
The SaaS Problem
You are building a SaaS and each customer can add users, but users can only be added once, hence we want unique emails. However, the email must not be unique for all customers therefore we have to make this rule: For each unique customer id, all emails must be unique.
Use the Rule-class
The problem is that you can no longer use the basic unique:email
rule anymore as this would block other customers from inviting that user.
The solution is to implement Laravels Rule
-class.
return [
'email' => Rule::unique('users', 'email')->where(function($query) {
$query->where('customer_id', User::findOrFail($this->user)->customer_id)
->where('id', '<>', $this->user);
}),
];
Syntax Explained
The concept here, as said, is to ensure the email is only unique across that customer’s users. Let’s look at the syntax:
return [
// Tell Laravel which column it looks to be unique. Typically the same as on the left side
'email' => Rule::unique($table, $column)->where(function($query) {
// Add the extra criteria column, filtering the lookup to only those belonging to the same customer as the current user is
$query->where($filterColumnCriteria, User::findOrFail($this->user)->{$filterColumnCriteria})
// Last, skip the user being update avoiding error because it finds itself
->where('id', '<>', $this->user);
}),
];
Implementation
The round up in a sample Request file:
<?php
namespace App\Http\Requests;
use Illuminate\Foundation\Http\FormRequest;
class User 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()
{
return [
'email' => Rule::unique('users', 'email')->where(function($query) {
$query->where('customer_id', User::findOrFail($this->user)->customer_id)
->where('id', '<>', $this->user);
}),
];
}
}