Laravel validation: unique with multiple columns and soft_delete

Question

I am trying to do a Laravel validation rules as follow:

"permalink" => "required|unique:posts,permalink,hotel_id,deleted_at,NULL|alpha_dash|max:255",

The explanation to the rules is:

I have a table "Posts" in my system with the following fields (among others): hotel_id, permalink, deleted_at. If MySQL would allow make an unique index with null values, the sql would be:

ALTER TABLE `posts` 
ADD UNIQUE `unique_index`(`hotel_id`, `permalink`, `deleted_at`);

So: I just add a new row IF: the combination of hotel_id, permalink and deleted_atfield (witch must be NULL) are unique.

If there is already a row where the permalink and hotel_id field are the same and 'deleted_at' field is NULL, the validation would return FALSE and the row wouldnt be inserted in the database.

Well. I don't know why, but the query Laravel is building looks like:

SELECT count(*) AS AGGREGATE FROM `posts` 
WHERE `hotel_id` = the-permalink-value AND `NULL` <> deleted_at)

What the heck...

The query I was hoping Laravel build to validation is:

SELECT count(*) AS AGGREGATE FROM `posts` 
WHERE `permalink` = 'the-permalink-value' AND `hotel_id` = ? AND `deleted_at` IS NULL

Could someone explain me how this effectively works? Because everywhere I look it looks like this:

$rules = array(
   'field_to_validate' =>
   'unique:table_name,field,anotherField,aFieldDifferentThanNull,NULL',
);

Does anyone could help me?

Thank you

It sounds like you're trying to do a pretty complex validation in a very simple format. Rules are meant to be very simple. If you want something more complex, specific, you can create your own validator callbacks. In the Validator class, add a function validateSuperUnique, and use it as 'superunique'. — Rudie

ppalmeida ppalmeida · Accepted Answer · 2015-07-13T03:44:12

all.

Finally, I got a proper understanding of the validation (at least, I think so), and I have a solution that, if it is not beautiful, it can helps someone.

My problem, as I said before, was validate if a certain column (permalink) is unique ONLY IF other columns values had some specific values. The problem is the way Laravel validation string rules works. Lets get to it:

First I wrote this: "permalink" => "required|unique:posts,permalink,hotel_id,deleted_at,NULL|alpha_dash|max:255",

And it was generating bad queries. Now look at this:

'column_to_validate' => 'unique:table_name,column_to_validate,id_to_ignore,other_column,value,other_column_2,value_2,other_column_N,value_N',

So. The unique string has 3 parameters at first: 1) The table name of the validation 2) The name of the column to validate the unique value 3) The ID of the column you want to avoid (in case you are editing a row, not creating a new one).

After this point, all you have to do is put the other columns in sequence like "key,value" to use in your unique rule.

Oh, easy, an? Not so quickly, paw. If you're using a STATIC array, how the heck you will get your "currently" ID to avoid? Because $rules array in Laravel Model is a static array. So, I had to came up with this:

  public static function getPermalinkValidationStr() {
    $all = Input::all();
    # If you are just building the frozenNode page, just a simple validation string to the permalink field:
    if(!array_key_exists('hotel', $all)) {
      return 'required|alpha_dash|max:255';
    }

    /* Now the game got real: are you saving a new record or editing a field? 
If it is new, use 'NULL', otherwise, use the current id to edit a row.
*/
    $hasId = isset($all['id']) ? $all['id'] : 'NULL';

# Also, check if the new record with the same permalink belongs to the same hotel and the 'deleted_at' field is NULL:

    $result = 'required|alpha_dash|max:255|unique:posts,permalink,' . $hasId . ',id,hotel_id,' . $all['hotel'] . ',deleted_at,NULL';
    return $result;
  }

And, in the FrozenNode rules configuration:

'rules' => array(
    'hotel_id' => 'required',
    'permalink' => Post::getPermalinkValidationStr()
  ),

Well. I dont know if there is a easiest way of doing this (or a much better approach). If you know something wrong on this solution, please, make a comment, I will be glad to hear a better solution. I already tried Ardent and Observer but I had some problems with FrozenNode Administrator.

Thank you.

Laravel validation: unique with multiple columns and soft_delete

1 Answers