Request Validation Issue on Users Table

Hey everyone. I am trying to write the logic for updating a users account on the users table. I made a http request validator and I want a "unique" property on the username column. In the laravel docs it it says I can force the validator to ignore a particular ID when running the validation. This is exactly what I want to do. I want the unique property to check all columns except the authorized user whose username will obviously be the same unless they manually made the change.

Here is my request validator

<?php namespace App\Http\Requests;

use App\Http\Requests\Request;
use Illuminate\Support\Facades\Auth;

class UserRequest extends Request {

    /**
     * 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 [
            'name' => 'required|min:4',
            'username' => 'required|min:2|unique:users, username,' . Auth::id()
        ];
    }

}

But, I am getting a sql error that I cannot fully comprehend what they are trying to tell me.

SQLSTATE[42S22]: Column not found: 1054 Unknown column ' username' in 'where clause' (SQL: select count(*) as aggregate from users where username = deevoweb and id <> 1)

The error output then starts like this:

in Connection.php line 620 at Connection->runQueryCallback('select count() as aggregate from users where username = ? and id <> ?', array('deevoweb', '1'), object(Closure)) in Connection.php line 576 at Connection->run('select count() as aggregate from users where username = ? and id <> ?', array('deevoweb', '1'), object(Closure)) in Connection.php line 293 at Connection->select('select count(*) as aggregate from users where username = ? and id <> ?', array('deevoweb', '1'), true) in Builder.php line 1377

I am really at a loss here as I need the validation on the username, but making sure to stick to the best practices in laravel validation. Any help is greatly appreciated.

BrandonSurowiec

11 years ago

Level 14

The SQL error says that there is no username column on your users table. Looks like you need to add it using a migration.

php artisan make:migration add_username_to_users_table --table=users

That will generate a file in your database/migrations/ folder where you'll need to update the up() and down() methods.


use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AddUsernameToUsersTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function(Blueprint $table)
        {
            $table->string('username')->unique();
        });
    }


    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function(Blueprint $table)
        {
            $table->dropColumn('username');
        });
    }

}

To use that dropColumn command you'll need to pull in doctrine/dbal into your composer.json.

Then simply

php artisan migrate

deevo

11 years ago

Level 5

Thanks @Niban , but I definitely have a username column. That is how I registered the user on the Users table. This is validation for updating a user who already exists. I used laravel's default Auth functionality for registering users and that works great. Every other field works fine for updating minus the username.

Laravel docs say you can ignore a record with a particular ID, but, I am still getting an error.

deevo

11 years ago

Level 5

Here is my migration:

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('username')->unique();
            $table->string('country');
            $table->string('city');
            $table->string('password', 60);
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('users');
    }

}

BrandonSurowiec

11 years ago

Best Answer

Level 14

@deevoweb I looked closer at the error. It is showing no ' username' column -- with a space at the beginning. Update your rule to not have a space before username:

'username' => 'required|min:2|unique:users,username,' . Auth::id()

2 likes

deevo

11 years ago

Level 5

Yep, I actually just found that myself was coming back to update that I found the error. Those dang spaces. So natural to insert, yet so troublesome!

1 like

Please or to participate in this conversation.