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?

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2qXBxA2
via IFTTT