CODEWITHSUNDEEP
phplaravel
Cornel Verster
Cornel Verster

Reputation: 1781

Applying a WHERE clause to laravel return scopes

I have a scope on one of my laravel models to return data related to a model contained in another table:

public function scopeWithShortlistedApplications($query)
{
    $query->with('shortlisted_applications:shift_id,user_id,shortlisted');
}

Here, shortlisted_applications is the relationship and refers to a table called shift_user which has the Application model attached to it, defined as follows.

public function shortlisted_applications() 
{
    return $this->hasMany(Application::class, 'shift_id');
}

What I want to do, is put a simple WHERE clause on the scope, so that it only returns instances where the "shortlisted" field is equal to 1 (this is a boolean value). I tried the following two techniques, but to not avail:

public function scopeWithShortlistedApplications($query)
{
    $query->with('shortlisted_applications:shift_id,user_id,shortlisted')
        ->whereRaw('SELECT shortlisted from shift_user WHERE shift_id = "' . $this->id '"', 1);
}

and

public function scopeWithShortlistedApplications($query)
{
    $query->with('shortlisted_applications:shift_id,user_id,shortlisted')
        ->where('shift_user.shortlisted', 1);
}

Does anyone know how to accomplish this in the scope function?

Upvotes: 0

Views: 482

Answers (1)

lagbox
lagbox

Reputation: 50481

Eager loading constraints should be able to do it for you:

...->with(['shortlisted_applications' => function ($query) {
    $query->where('shortlisted', 1);
}]);

This would still load the main result set but only eager load the relationship for those records if they have shortlisted == 1.

Upvotes: 1

Related Questions