#Validate based on all lower case

21 messages · Page 1 of 1 (latest)

bitter coral
#

CODE

        $validator = Validator::make($request->all(), [
            'name' => [
                'required',
                'string',
                'max:255',
                Rule::unique('users')->where(function ($query) use ($request) {
                    return $query->whereRaw('LOWER(name) = ?', strtolower($request->name));
                })->ignore($request->user()->id),
                'regex:/^[a-zA-Z0-9_-]+$/',
            ],
        ]);

So the issue is, I want to validate based on lowercase. So, regardless of what the user types or what is in the DB, it looks for the same string all in the same case. The reason for this is users might type JohnSmith for their username and we respect the case, but we do not want another user with the name johnsmith or JOHNsmith.

What is wrong with the above query?

teal hornet
bitter coral
#

username in DB = RKMAdmin - I type rkmadmin as a new username and it says its valid... It should come back as "taken" or false

#

To be fair, I have tried this 50 ways but it never quite gets it

teal hornet
bitter coral
#

To be fair, I checked about iteration 20 LOL - at this point I was grasping - let me go back to the drawing board...

teal hornet
#

I wonder if unique accepts a builder closer as a second argument to
Rule::unique('users', function (Builder $query) {... instead to override the default condition

#

Not got code in front of me to check but you should determine the query its running first before making any changes

bitter coral
#

ok so in that case... yes the query is running at some level - but I do not know if its handling the lower still... so I do need to test that

teal hornet
#

Telescope makes this easy...

bitter coral
#

yep loading it now

teal hornet
bitter coral
#

LOL, you wont even believe it...

# 
$validator = Validator::make($request->all(), [
            'name' => [
                'required',
                'string',
                'max:255',
                Rule::unique('users')->where(function ($query) use ($request) {
                    return $query->whereRaw('LOWER(name) = ?', [strtolower($request->name)]);
                })->ignore($request->user()->id),
                'regex:/^[a-zA-Z0-9_-]+$/',
            ],
        ]);

Thats the fix

#

specifically [strtolower($request->name)]

teal hornet
bitter coral
#

Nope. Does not appear to.

teal hornet
#

weird cos you can do Rule::unique('users')->ignore(..) and it will do a where on the name field automatically

#

@bitter coral just checked your query is this....

"select count(*) as aggregate from `users` where `name` = ? and (LOWER(name) = ?)"
array:2 [
  0 => "Daniel Mason"
  1 => "daniel mason"
]
bitter coral
#

I’ll check telescope again but it only showed a single where. Maybe I changed it and don’t remember. I’m not at my laptop right now.

teal hornet
# 
DB::listen(fn ($q) => dump($q->sql, $q->bindings));
Validator::make(['name' => 'Daniel Mason'], [
    'name' => [
        'required',
        Rule::unique('users')->where(fn (Builder $q) => $q->whereRaw(
            'LOWER(name) = ?',
            [strtolower('Daniel Mason')],
        )),
    ],
])->validate();