CODEWITHSUNDEEP
phpmysqllaravellaravel-5laravel-5.3
tom f
tom f

Reputation: 389

Laravel scopes and joins not honoring where statement for relations

been using Laravel 5 for quite some time now, and though I love it there's this occasional problem I seem to run into with Eloquent and Query builder that drives me nuts. I've been trying to find workarounds for a long time and cannot figure it out for the life of me.

Long story short I've got an Artwork model that belongsTo an Artist model; conversely the Artist hasMany Artworks, on Laravel 5.3 + MySQL.

Both the Artwork model and Artist have a property visible. What I need is for a query on all Artwork models to only ever return results that have an Artwork that belongs to an Artist model, where Artwork and Artist both have a visible value of 1.

I've tried this a bunch of different ways and I know I have had no problems with this in the past. Usually I do an Eloquent based query, either manually or with scopes on each model. Broken down without scopes I've done this:

$artwork = Artwork::with([ 'artist' => function($query) {
                $query->where('visible', 1);
            }])->where('artworks.visible', 1)->get();

I get results and no errors, which is great. Except a certain percentage of the results have an Artist model attached where the Artist's visible property = 0. Doesn't make sense. So I figured maybe it was a table naming thing and the with where statement was pointing to the artwork table by mistake.

Tried to be more explicit on the Eloquent query and changed the with/where to:

$query->where('artists.visible', 1);

No love, o then I tried to just make a good old fashioned query builder query like this:

$artwork = DB::table('artworks')
                ->join('artists', function ($join) {
                    $join->on('artists.id', '=', 'artworks.artist_id')->where('artists.visible', '=', 1);
                })
                ->where('artworks.visible', '=', 1)
                ->get(); /// get or paginate or whatever

So basically the same thing but with query builder. And once again, no errors, I get my results. But it's the same thing where I am getting results where Artist models have a visible value of 0!

My inkling is I am making some very rudimentary mistake and I've been looking at this for way too long to realize. Seems weird and I'm to the point where it seems like my with/join statement is just getting ignored. Any thoughts?

EDIT for answer context:

The answers below nailed it, but I am linking to this post to give context of why this wasn't working for posterity.

Regarding Laravel Eloquent's has() vs where() vs whereHas()

There are differences between these that can be confusing, especially in regard to accepting arguments and subqueries. Read this for more info: Laravel - Eloquent "Has", "With", "WhereHas" - What do they mean?

Upvotes: 0

Views: 372

Answers (2)

Harshal Shah
Harshal Shah

Reputation: 427

Try this!!

$results = Artwork::whereHas('artist', function ($q) {
       return $q->where('visible', 1); 
       })
	    ->with('artist')
	    ->where('visible', 1)
	    ->get();

Upvotes: 1

Sandeesh
Sandeesh

Reputation: 11906

$artwork = Artwork::whereHas('artist', function ($query) {
        $query->where('visible', 1);
    })
    ->with('artist')
    ->where('visible', 1)
    ->get();

$artwork = DB::table('artworks')
    ->join('artists', 'artists.id', '=', 'artworks.user_id')
    ->where('artists.visible', 1)
    ->where('artworks.visible', 1)
    ->get();

Upvotes: 1

Related Questions