Laravel Validation unique/exists with different database connection

Question

In the documentation, I saw you could set a connection for the unique rule which is great. However, the exists doesn't seem to follow the same logic. Take this for example:

$rules = [
    'username'         => 'required|max:40|unique:user',
    'name'             => 'sometimes|required',
    'email'            => 'required|email|max:255|unique:int.user',
    'password'         => 'sometimes|required|confirmed|min:6',
    'password_current' => 'sometimes|required'
];

The unique rule works GREAT in this instance. It uses my database connection called 'int' and calls the user table. HOWEVER, when the rules are reversed like so:

$rules['email'] = 'required|email|max:255|exists:int.user';

I got this error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'int.user' doesn't exist (SQL: select count(*) as aggregate from int.user where email = [email protected])

It's trying to call an int.user table instead of using the int database connection.

Is there a reason exists doesn't act the same way as unique? Thanks.

Ardeliaardelis

I don't know the reason, but there is no connection parameter for exists. According to the documentation: exists:table_name,field_name – Ghetto

it works in the latest version L5.1.17 – Colossian

Answer 1

instead of using connection name you can try with straight Database name which is defined in "int" connection. faced similar problem and these way worked for me. like

$rules['email'] = 'required|email|max:255|exists:DB_Name.user';

Bogbean

Important note: this does not work in Laravel 5.1.46. Somewhere along the line the validation code was changed to parse the "exists" expression as "connection.table", breaking the use of a database name. One possible solution is to define multiple database connections in config/database.php, one for each database you use in an "exists" rule. – Heterochromous

exists:db_name.table_name,coulmn_name works on Laravel 9.x – Lenticular

Answer 2

You can use

'email'           => 'exists:mysql2.users|required'

Where mysql2 is second database settings array in the database.php file

Bandit

Answer 3

Try it.

$rules = [
     'username'         => 'required|max:40|unique:connection_name.user',
     'name'             => 'sometimes|required',
     'email'            => 'required|email|max:255|unique:connection_name.user',
     'password'         => 'sometimes|required|confirmed|min:6',
     'password_current' => 'sometimes|required'
];

Curvaceous

Answer 4

Ultimately for Laravel 5.6.* you need to look at an existing instance of the model you are trying to validate, or specify ...

{db_connection_name}.{schema_name}.{table_name}

... to ensure that you are looking at the proper table.

Validation Example

validate it...

<?php

// for instance... 
//   maybe auth user is in a different db
//   = so you cannot validate with your default db connection
$default_user = Auth::user();

// pass the instance in order to allow Validator to qualify the proper connection/name
\App\Validation\User::validate($_POST, $default_user);

User Validation class

<?php
namespace App\Validation;

class User extends Validator
{
    /**
     * @param \Illuminate\Database\Eloquent\Model|string $mixed
     * @param string $default
     * @return string
     */
    public static function table($mixed,$default='default_connection.app_schema.users_table')
    {
        if($mixed instanceof \Illuminate\Database\Eloquent\Model){
            $table = $mixed->getConnectionName().'.'.$mixed->getTable();
        } else {
            if (! empty($mixed)) {
                $table = $mixed;
            } else {
                $table = $default;
            }
        }
        return $table;
    } 

    /**
     * validation to create a new user
     *
     * @param array $data
     * @param \App\User|string $mixed
     * @return array
     * @throws \Illuminate\Validation\ValidationException
     */
    public static function validate(array $data, $mixed='default_connection.app_schema.users_table'){
        return Validator::validate($data,[
            'username'         => 'required|max:40|unique:'.self::table($mixed),
            'name'             => 'sometimes|required',
            'email'            => 'required|email|max:255|unique:'.self::table($mixed),
            'password'         => 'sometimes|required|confirmed|min:6',
            'password_current' => 'sometimes|required'
        ]);
    }
}

Milli

Answer 5

$default_connection = 'db_name';
    $rules = [
        'username'         => 'required|max:40|unique:{$default_connection}.user',
        'name'             => 'sometimes|required',
        'email'            => 'required|email|max:255|unique:int.user',
        'password'         => 'sometimes|required|confirmed|min:6',
        'password_current' => 'sometimes|required'
    ];

Hysterotomy

Answer 6

If you use multiple Db Connections then we can check Uniquess value as like below

use Illuminate\Validation\Rule;

'field_name' => [ 
      Rule::unique('connectionName.tableName','DbColumnName')
]

but during update operation, To instruct the validator to ignore the table's primaryKey then we can do as like below

$idFromInput = "here pass id from input request";
'field_name' => [ 
      Rule::unique('connectionName.tableName','DbColumnName')->ignore($idFromInput, 'idColumnName')
]

for my case I use Mysql as default connection & MongoDb as a another connection & if i need to check uniqueness value in MongoDb Collection than I can do it as like below

In MongoDb by default $primaryKey='_id'

$rules = [
    'email' => [
         'required', 
         Rule::unique('mongodb.studentTblName','primary_email_DbColumn')->ignore($request->_id, '_id')
    ]
]

Abortionist