How to validate two column unique where one column is nullable in in laravel

Question

I have the following database table structure for an agents table

+--------------------------+---------------------+------+-----+---------+----------------+
| Field                    | Type                | Null | Key | Default | Extra          |
+--------------------------+---------------------+------+-----+---------+----------------+
| id                       | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| first_name               | varchar(255)        | NO   |     | NULL    |                |
| phone                    | varchar(255)        | NO   | MUL | NULL    |                |
| active                   | tinyint(1)          | YES  |     | NULL    |                |
+--------------------------+---------------------+------+-----+---------+----------------+

the fields phone and active is having a 2 column unique constraint, meaning only one phone should be in the active state at once. the column active is a nullable boolean, because if I put 0 for the falsy value then the 2 columns unique constraint will fail if there are two inactive phone numbers (since null != null it is working fine on the database level when I manually insert it in database). I hope I am clear here.

The Problem

I am trying to set up the validation rules for this feature. So while creating an agent if there is any active phone numbers the validation should fail, otherwise, the record should be inserted.

here is my validation rule

$request->validate([
   'first_name'=> 'required',
   'active' => 'required',
   'phone' => [
       'required', 
       Rule::unique('delivery_agents')->using(function ($query) use ($request) {
             $query->where('phone', $request->phone)
                 ->where('active', $request->active ? $request->active : null); // explicitly checking against null value or true not 0
       })
    ],
 ]);

But the validation rule fails when there is one inactive phone number (ie: phone number with active = null)

I believe the reason is that the validation rule checks for phone number with active = null and since there is one record like that it throws the validation failed exception. How do I mitigate this.

Found a post here but again it's the same thing I have posted in the question.

Using Laravel 6, and Mysql 8

score 0 · Answer 1 · answered Apr 19 '20 at 06:29

0

here you go

$request->validate([
   'first_name'=> 'required',
   'active' => 'required',
   'phone' => [
       'required', 
       Rule::unique('delivery_agents')->using(function ($query) use ($request) {
             $query->where('phone', $request->phone)
                 ->where(function ($query) {
                      $query->where('active', 1)
                            ->orWhereNull('active');
}); 
       })
    ],
 ]);

answered Apr 19 '20 at 06:29

phpdroid

1,642
1
18
36

Doesnt work for me, it's the same outcome, There can be multiple non active phone numbers – Shobi Apr 19 '20 at 06:58
why dont you just check active? – phpdroid Apr 19 '20 at 07:11
what you mean ? – Shobi Apr 19 '20 at 08:23
can you explain a little bit clear what should be expected input and output? – phpdroid Apr 19 '20 at 09:39

score 0 · Answer 2 · answered Apr 19 '20 at 07:31

0

Checking not null is enough as you have only 1 and NULL as possible values for active

'phone' => [
    'required', 
    Rule::unique('delivery_agents')->where(function ($query) use ($request) {
         return $query->where('phone', $request->phone)
               ->whereNotNull('active'); 
    })
],

answered Apr 19 '20 at 07:31

Vinay

7,442
6
25
48

Indeed try removing `return` maybe? – Vinay Apr 19 '20 at 08:26
still no luck. Let me dig into the Rule class to figure this out – Shobi Apr 19 '20 at 08:32

How to validate two column unique where one column is nullable in in laravel

2 Answers2