Laravel unique validation with where and join

2
votes

I'm trying to add aditional where clause in the unique validation rule. But before that, i need a join with other table. But I'm not being successful.

I have 2 Tables:

teams

with id, and name field

role_user_team

with role_id, user_id, and team_id being this three the key.

So i need to check if the teams.name field is unique for one specific user. I did try this:

'name' => Rule::unique('teams')->where(function ($query){
    //return $query->where('account_id', 1);
    return $query->join('role_user', 'role_user.team_id', '=', 'teams.id')->where('role_user', Auth::user()->id);
          });

I know the problem is with the join but i don´t know how to solve it. Thank you!

3
phplaravellaravel-5.5laravel-validation
Use Auth::id() instead of Auth::user()->id. Object Auth::user() can not exists. - ventaquil
I think join is not allowed in the where clause...so i need a new validation rule to check what i want. ¿Any idea of this? - Carlos Cardenal
Indeed, seems like joining is not functioning, although this is not documented anywhere. The closure gets a standard query builder object so you would expect joining to just work. But the querybuilder object seems to get edited afterwards, breaking joins. - aross

3 Answers

1
votes

This seems to be a bug in how Laravel handles the QueryBuilder object just before the actual query to DB is done. Printing the query log shows that no actual join is performed. It could also be an inadequacy in the documentation (under "additional where clauses"), because that doesn't mention a limitation on using the QueryBuilder.

The quickest solution is to extend the Validator on the fly, like this:

public function rules()
{
    Validator::extend('custom_rule', function ($attribute, $value) {
        // The user that's being edited, if any.
        $currentUser = $this->route('user');

        $query = User::join('addresses', function ($join) {
            $join->on('addresses.user_id', 'users.id')
                ->where('addresses.address_type', 'home');
        })->where($attribute, $value)->where('users.id', '!=', $currentUser->id ?? 0);

        // True means pass, false means fail validation.
        // If count is 0, that means the unique constraint passes.
        return !$query->count();
    });

    return [
        'name' => 'required|custom_rule',
    ];
}
0
votes

Laravel 5.8 - problem is still exists.

See \Illuminate\Validation\DatabasePresenceVerifier class, method addConditions().

It can be clearly seen that if a validation rule condition is a closure, then it is performed inside the nested where section, which excludes the possibility of using joins:

/**
 * Add the given conditions to the query.
 *
 * @param  \Illuminate\Database\Query\Builder  $query
 * @param  array  $conditions
 * @return \Illuminate\Database\Query\Builder
 */
protected function addConditions($query, $conditions)
{
    foreach ($conditions as $key => $value) {
        if ($value instanceof Closure) {
            $query->where(function ($query) use ($value) {
                $value($query);
            });
        } else {
            $this->addWhere($query, $key, $value);
        }
    }

    return $query;
}

And yes, the only one way to use join in validation rule - is make your own rule, like aross sayd.

-1
votes

Look at the documentation in Advanced Join Clauses

$query->join('role_user', function ($join) {
    return $join->on('role_user.team_id', '=', 'teams.id')->on('role_user', '=', Auth::id());
});