Laravel: Unique Validation with Another Column

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);
            }),
        ];
    }
}