Laravel 5.5 - Custom Validator - Check SECOND UNIQUE Column Of Database

Issue

Laravel 5.5 has the ability to create custom validators, and all the examples I have seen are items like "make sure it's not more than 5 characters", "make sure it's a proper phone number", etc.  Those are GREAT.  However, I needed my validator to be a bit more for a specific situation.

Here's the situation:

I have a Growers table, and each grower has an autogenerated "id" field, but can also have an "assigned_grower_id" as a second column.  This value can be a number, text, whatever - but it must be unique. Examples of this might be "store number 2345" or "My Favorite Grower" . 

 I have setup the migration so that the table has the row "unique" tag placed on it.  When building the database using "php artisan migrate" the column will be built with the unique requirement and an index will be built based off that column.  GREAT.

When building the create function in my controller, I can use Laravel's built in "unique" constraint because I want to make sure that the "assigned_grower_id" does not already exist in the database. Cool.  All Good.

Request - "Users need to have the ability to update the assigned grower id."

Herein lies the problem.  When creating the "update" function off my controller, I can no longer use the "unique" constraint again.  This is because the user may NOT be updating the existing "assigned_grower_id" but I need to check for it.  This is to make sure someone hasn't accidentally deleted it OR making sure that they are not passing through a value already assigned to another grower.  We can keep the "required" option but not the unique.  BUMMER.

Problem I need to solve

Let's say a grower exists with an assigned_grower_id of "My Favorite Grower" andanother grower is set with an assigned_grower_id of "My Second Favorite Grower".   Both of these will work.

Now, suppose we want to change the second grower's assigned_grower_id to "My Favorite Grower". 

Since we only have the "required" validator on this field (and not the unique validator), Laravel would allow the request through to the database.  The request would fail at the database level (because we assigned the unique constraint on the table itself) so the data would NOT get into the database.  However, the database returns a nasty "unique constraint violation" which we then have to handle, and figure out how to add it to Laravel's default ErrorBag.  NAW. I think not. Custom Validators TO THE RESCUE.

What's the overall validation that needs to happen:

  1. If a user is updating a grower, and passes through an assigned_grower_id
  2. We need to look in the database and make sure that the assigned_grower_id is NOT already in use by ANY GROWER OTHER THAN THE CURRENT GROWER FROM OUR REQUEST.
    1. This check will allow a user to pass through the grower's current assigned_grower_id.
  3. This custom validator needs to be usable throughout my application
    1. Creating a one off custom "VerifyAssignedGrowerIDHasNotBeenUsed" is a waste of time. 
    2. Creating a "NotAlreadyAssigned" validator that works anywhere...well...(kisses fingers like chef).


Solution

Image 1:

cv1.png

App\Rules\NotAlreadyAssigned.php

Image 2:

cv2.png

Image 3

cv3.png
  1. Create a new rule using "php artisan make:rule NotAlreadyAssigned"  (image 1)
  2. We will need to pass two pieces of information into this validator: (image 2)
    1. The model that we need to query
      1. I am calling this "modelToCheck"
    2. The "allowed ID" of the grower that is allowed to have the assigned grower id
      1. I am calling this "allowedId"
  3. Create protected variables for the items we'll be passing in, and then set them up in your constructor. (image 2)
    1. When we pass in the model we need, the validator converts it to a string. This is important for the handle method.
  4. In the handle method, we'll need to convert the string "model" back into a class.  To do that we use the app($this->modelToCheck) function, and pass in our model variable that we set in the constructor. (image 3)
    1. This gets us all of the capabilities as if we had called \App\Grower::class, which means we can now use Eloquent to filter some results. YEAH BOYYYEEE.
  5. Our first where clause checks to see if the 'assigned_grower_id' that was passed in exists in the database already.  (image 3)
    1. Remember, it might exist because our CURRENT grower already has it assigned OR another grower could be using it.
  6. Our second clause filters out the "allowedID".  (image 3)
    1. This is the ID of the current grower that we are updating.
    2. If this grower already has this assigned_grower_id assigned, then we'll remove it from the returned results.
  7. This will leave us only with growers that don't have our current primary key ID, but DO have the assigned_grower_id that can be set to anything. (image 3)
  8. We apply a "->count()" to this. Anything greater than 0 means another grower is currently using that "assigned_grower_id" and we will return "false" for the validation. (image 3)

OK How Do We Call This Monstrosity?

Within Your Controller's Update function:

cv4.png

Notice that we are passing in the "Grower::class" and $growerId.  We will be able to reuse this validator for ANY other models we wish to test by passing in the same type of information.  For example, if we needed to verify a second column for a "Customer", we'd pass in Customer::class, and $customerId (or whatever the id is of the current model you are attempting to update).

How Does One TEST This Thing?

cv5.png
  1. In the above method, "$this->editGrower" is a helper command that just takes the grower that I have created already, and posts the update to the appropriate end point.