laravel belongstomany with condition

Question

I have the following model.

class Training extends \Eloquent {

    // Add your validation rules here
    public static $rules = [
        'name' => 'required',
        'city' => 'required',
        'province' => 'required',
        'budget_year' => 'required|integer',
        's_date' => 'required|date',
        'e_date' => 'required|date'
    ];

    // Don't forget to fill this array
    protected $fillable = [
        'name',
        'city',
        'province',
        'budget_year',
        's_date',
        'e_date'
    ];

    public function material(){
        return $this->hasMany('Material');
    }

    public function budget(){
        return $this->belongsToMany('Budget')->withPivot('amount');
    }

    public function budgetById($training_id){
        $this->belongsToMany('Budget')->where('training_id', '=', $training_id)->get();
    }

}

when I debug the budgetById method using DB::getQueryLog, the query is as follow

select budgets.*, 
budget_training.training_id as pivot_training_id, 
budget_training.budget_id as pivot_budget_id 
from budgets inner join budget_training on budgets.id = budget_training.budget_id 
where budget_training.training_id is null and training_id='6'

which return 0 rows, but when I try to modify the query and run it in pgadmin, the following script works well.

select budgets.*, 
budget_training.training_id as pivot_training_id, 
budget_training.budget_id as pivot_budget_id 
from budgets inner join budget_training on budgets.id = budget_training.budget_id 
where budget_training.training_id='6'

notice I remove training_id is null and from Laravel generated query. What is wrong with my budgetById method?

Solution 1

You have called get() and didn't use return here:

public function budgetById($training_id){
    // = in where is optional in this case
    $this->belongsToMany('Budget')->where('training_id', '=', $training_id);
}

You should use like this:

public function budgetById($training_id){
    // = in where is optional in this case
    return $this->belongsToMany('Budget')->where('training_id', '=', $training_id);
}

Solution 2

In Lavarel 7.X, you can use the wherePivot method to filter columns on the pivot table, like this:

return $this->belongsToMany('Budget')->wherePivot('training_id', '=', $training_id);

or

return $this->belongsToMany('Budget')->wherePivotNotNull('training_id');

Comments

[Ben]

You don't need = as if there are only 2 parameters on where(), = is implied

[Ayaz Ali Shah]

Can I add more than one where clause ?

[The Alpha]

Yes you can @AyazShah

[highjump]

How can I use this budgetById in with function?

[The Alpha]

YourModel::with('budgetById')

[Lakhveer Bawa]

Hi @alpha how can we pass the training id?