Exists validations with multiple tables

Question

Laravel version (7.x).

I have three tables companies, complaint_types & complaints.

The complaints are associated with a company.

When a complaint is assigned to an employee then only he/she can see and enter the complaint status after the visit. Before entering the comments the complaint must be validated, otherwise the comment must not be allowed to enter, which is handled upon submitting the form via a hidden field called complaint_id.

I have added this validation because I don't want anyone opening the inspect tool, playing with the values and causing the application an error.

Tables:

companies:  ->  complaint_types:  ->  complaints:
|-- id          |-- id                |-- id
|-- ...         |-- company_id        |-- complaint_type_id
                |-- ...               |-- ...

Complaint.php:

public function complaintType()
{
    return $this->belongsTo(ComplaintType::class);
}

ComplaintType.php:

public function company()
{
    return $this->belongsTo(Company::class);
}

public function complaints()
{
    return $this->hasMany(Complaint::class);
}

ComplaintController.php:

private function validate($data)
{
    # variables
    $rules = [
        'complaint_id' => [
            'required', 
            'exists:complaints,id,complaintType.company_id,' . session()->get('COMPANY')
        ],

        -- OR --

        'complaint_id' => [
            'required', 
            'exists:complaints,id,complaint_types.company_id,' . session()->get('COMPANY')
        ],

        ...
    ];

    $messages = [
        'complaint_id.required' => '`Complaint` - Required<br>',
        'complaint_id.exists'   => '`Complaint` could not be identifed<br>',

         ...
    ];

    return Validator::make($data, $rules, $messages);
}

Error
Column not found: 1054 Unknown column 'complaintType.company_id' in 'where clause' (SQL: SELECT COUNT(*) as aggregate FROM complaints WHERE id = 3 AND complaintType.company_id = 1)

Answer 1

Problem solved, Here is my code:

$rules = [
    'complaint_id' => [
    'required',
    function($attribute, $value, $fail) use ($data) {
        $Complaint = Complaint::where('id', $value)
            ->whereHas('complaintType', function($query) use($data) {
                return $query->where('company_id', session()->get('COMPANY'));
            })->count();

        if($Complaint <= 0)
            $fail('`Complaint` could not be identified');
    }
],

I found the reference here How can I add a join to a custom exists rule for a laravel validator?

Answer 2

You should specify relationship with primary key like:

public function complaints(){
return $this->hasMany(Complaint::class, 'complaint_type_id','id');
}