Can't filter through Relationship in Laravel Datatables

Question

so i have a Laravel Datatables project and i'm trying to create a column that, when you click a specific value it will send an AJAX request to the PHP backend and will try to FILTER the results on the DataTable by a specific column and that value which you clicked on.

   $sign = Request::input('sign');

    return $this->datatables
        ->eloquent($this->query())
        ->filter(function ($query) use ($sign) {
            if (request()->has('sign')) {
                $query->where('signs.id', "'$sign'");
            }
        }, true)
        ->addColumn('admin', function($user) {
            return $user->admin == 1 ? '<span class="fa fa-star"></span>' : '<span class="fa fa-star-o"></span>';
        })...

The problem there is that in the filter method i can't do the Where clause on the related signs table. It's trying to query the signs.id column in the users table, which is not what i want.

On my Model i have the relationship defined like this

public function signs(){
    return $this->belongsTo('App\Models\Sign', 'sign_id', 'id');
}

And i'm issuing a base query to the Datatable which defined the User model with signs like this

public function query()
{
    $query = User::with('signs');

    return $this->applyScopes($query);
}

What am i doing wrong? Why can't i filter my users through this relationship? Any help will be greatly appreciated as i've already spent hours on it and can't figure it out on my own.

score 2 · Answer 1 · edited May 06 '18 at 19:57

I think I've finally figured it out, i'm getting rusty as I've done this sort of thing before and simply couldn't recall it...

 return $this->datatables
        ->eloquent($this->query())
        ->filter(function ($query) use ($sign) {
            if (request()->has('sign')) {
                $query->whereHas('signs', function($q) use($sign){
                    $q->where('sign', $sign);
                });
            }
        }, true)

Just do a whereHas and rebind the 'signs' reference and then query that.

Can't filter through Relationship in Laravel Datatables

1 Answers1