How can I add a join to a custom exists rule for a laravel validator?

Question

The validator in laravel can have a customization of the exists database rule, for instance if you need to check an extra column. An example from the manual:

use Illuminate\Validation\Rule;

Validator::make($data, [
    'email' => [
        'required',
        Rule::exists('staff')->where(function ($query) {
            $query->where('account_id', 1);
        }),
    ],
]);

The query in the closure is not typehinted, so i'm not quite positive what kind of object this is. I can see that the DatabaseRule itself only has some function about where, wherenot etc, but i'm looking to add a join to the mix.

Given example says that the email must exist for a staff with account_id = 1, but what if the team (all staff are part of a team, which is a separte table) should have a certain property, e.g. team.active = 1 ?
^{the whole staff/team thing is an example of course}

---

So in the end I'm wondering: how can I add a join to this rule so we make sure that the staff's team has a column 'active' that is 1.

My first issue might be: what is the type of that $query? I would imagine that something like this would be great, but there's no reason to suspect this is valid:

Rule::exists('staff')->where(function ($query) {
    $query
        ->join('team', 'team.team_id', '=', 'staff.team_id')
        ->where('team.active', 1);
})

This does not seem to work. The strange thing is that the join itself does not cause an error, but seems to be ignored:

Column not found:
1054 Unknown column 'team.active' in 'where clause'
(SQL: select count(*) as aggregate from staff where email = -thevalue- and (team.active = 1))

I would have expected this to work (small change as I'm gambling that this function is available here), or get an error because I'm calling a non-existent function. But what I get is a query that is build but without the join.

From the comments I added a $query->toSQL() to that function. This does show a rather expected result, but it does not compute with the error I'm getting:

select * from `staff`
inner join `teams` on `teams`.`team_id` = `staff`.`team_id`
where `teams`.`active` = ?

Laminous

Try to var dump $query->toSql() from inside the rule closure and see what you get. The $query is an instance of Illuminate\Database\Query\Builder, so you should have no problems making that join. – Inch

What about custom validation rules ?? doc here – Nebulosity

@Nebulosity yes, that would be the fallback, but I thought this would be the most readable / nicest way to do it. – Laminous

@TheFallen I assumed it would be the query as shown in the error, but I'll check it specifically. And that's pretty interesting indeed! I'll add it to the question – Laminous

@TheFallen so the join seems to be there inside the closure, but then it starts doing the count aggregation to see if there is a result, and it has lost it? Not sure how that works. – Laminous

Answer 1

It seems that the DatabaseRule does some magic with whatever you provided in that closure. In the end, it only seems to look at provided where clauses (see Exists::__toString). The joins have been saved as the echo inside the closure shows, but then the exists rule only looks at the where. That's the reason for the unknown column error it seems.

I did try the using function, which seems better suited than the where I had in there first, but that doesn't help, as the system makes it into a string, which forms a validation-string that just doesn't seem to support joins.

Long story short, I just created a custom validation rule with the passes function looking something like this:

public function passes($attribute, $value)
{
    $count = DB::table('staff')
        ->join('teams', 'teams.team_id', '=', 'staff.team_id')
        ->where([
            ['teams.active', '=', 1],
            ['staff.email', '=', $value]
        ])->count();

    return $count > 0;
}

Laminous