Laravel whereRelation returning unexpected result-CodePudding

I have 2 tables Artists and Artworks. The structure of which are:

Artists

id	name	age
1	Joe Blogg	22
2	Bob Duff	34
3	Mark Smith	25

Artworks

id	title	sold	price	artist_id
1	flowers	1	80.00	1
2	plants	1	50.00	2
3	boxes	0	100.00	2
3	threads	0	110.00	3

In the Artist Model I have a relation to artworks

public function artworks()
{
    return $this->hasMany(Artwork::class);
}

In the Artwork Model I have the relation to artists

public function artist()
{
    return $this->belongsTo(Artist::class);
}

I want to return all the artists with that have sold an artwork (sold is 1) and the name of the ans price of the sold artwork. The eloquent query I am using is:

 $artists = Artist::with('artworks')->whereRelation('artworks','sold','=',1)->get();

This does select all the Artists with sold artwork. However, if the artist has both a sold artwork and an unsold artwork it returns both. so the query would return:

id	Names	title	sold	price	artist_id
1	Joe Blogg	flowers	1	80.00	1
2	Bob Duff	plants	1	50.00	2
3	Bob Duff	boxes	0	100.00	2

When the artwork with the title Boxes has not been sold, so should not be in the results

How do I get it so that I only return sold artworks?

CodePudding user response：

Thanks Cody Brew

Almost the right answer - but I am now getting all Artists whether they've sold an Artwork or not. Although the subquery now only selects sold Artwork.

I have added back the whereRelation to your answer and it now apperas to work

$artists = Artist::with(['artworks' => function($query) {$query->where('sold', 1);}])->whereRelation('artworks','sold','=',1)->get();

CodePudding user response：

You can do like this.

$artists = Artist::with(['artworks' => function($query) {
    $query->where('sold', 1);
}])->get();

It will only return the artist those have sold the artworks