Level 7
Do you just want the banners with the right size and/or do you want to remove the advertisers when there is no banners with that size?
Apr 6, 2018
7
Level 4
Nested BelongsToMany query - How to filter unwanted results
I'm trying to query all the advertisers assigned to a domain, and return all the ads assigned to these advertisers which match a set of user variables, and that have banner ads.
// Domain Model
public function advertisers()
{
return $this->belongsToMany('App\Advertiser');
}
// Ad Model
public function banners()
{
return $this->hasMany('App\Banner');
}
// the other methods are belongsToMany()
return $this->domain->load([
'advertisers' => function ($q) {
return $q->with('ads.banners')
->whereHas('ads.banners', function ($q) {
$q->where('size', '=', '728x90');
})
->with('ads.countries')
->whereHas('ads.countries', function ($q) {
$q->whereIn('key', [session('country_code'), 'XX']);
})
->with('ads.operatingSystems')
->whereHas('ads.operatingSystems', function ($q) {
$q->whereIn('key', [session('os'), 'all']);
})
->with('ads.deviceTypes')
->whereHas('ads.deviceTypes', function ($q) {
$q->whereIn('key', [session('device_type'), 'all']);
})
->with('ads.browsers')
->whereHas('ads.browsers', function ($q) {
$q->whereIn('key', [session('browser'), 'all']);
});
}
]);
The query almost works, but returns banner relationship with wrong sizes because they are part of the same advertiser.
#relations: array:5 [▼
"banners" => Collection {#542 ▼
#items: array:2 [▼
0 => banner {#582 ▶} // <-------- correct size 728x90
1 => banner {#583 ▼
#table: "banners"
+timestamps: true
#dates: array:1 [▶]
#connection: "mysql"
#primaryKey: "id"
#keyType: "int"
+incrementing: true
#with: []
#withCount: []
#perPage: 15
+exists: true
+wasRecentlyCreated: false
#attributes: array:7 [▼
"id" => 26
"created_at" => "2018-04-06 16:54:40"
"updated_at" => "2018-04-06 16:54:40"
"deleted_at" => null
"size" => "160x600" // <-------------------------- wrong size
"s3_path" => "/banner/xN0Q9g6t94not3WOTnZ8IWR58wSa0rxBQfMBJUA1.png"
"ad_id" => 1
]
#original: array:7 [▶]
#changes: []
#casts: []
#dateFormat: null
#appends: []
#dispatchesEvents: []
#observables: []
#relations: []
#touches: []
#hidden: []
#visible: []
#fillable: []
#guarded: array:1 [▶]
#forceDeleting: false
}
]
}
"countries" => Collection {#546 ▶}
"operatingSystems" => Collection {#553 ▶}
"deviceTypes" => Collection {#620 ▶}
"browsers" => Collection {#655 ▶}
How can I write the query so it only returns the banners with the size I requested?
Level 4
hey @StefanoRuth thanks for your reply. I want the banners in the right size only, so exactly what the query returns now minus banners that do not have the correct size
Level 67
Why are you doing
->with(relation)
->whereHas([relation => filters])
for each one relationship?
The ->with() is getting all data for the relationship (because you have no restrictions on it), and your whereHas's is using a where to get what you're actually looking for.
It seems you're getting all data, and also the whereHas data.
Try just using the whereHas's.
Level 4
@Cronix thanks but this also doesn't work (I got rid if the unnecessary with() though)
rewritten:
return $this->domain->load([
'cpaAdvertisers' => function ($q) {
return $q->with('cpaAds.cpaBanners')
->whereHas('cpaAds.cpaBanners', function ($q) {
$q->where('size', '=', '728x90');
})
->whereHas('cpaAds.countries', function ($q) {
$q->whereIn('key', [session('country_code'), 'XX']);
})
->whereHas('cpaAds.operatingSystems', function ($q) {
$q->whereIn('key', [session('os'), 'all']);
})
->whereHas('cpaAds.deviceTypes', function ($q) {
$q->whereIn('key', [session('device_type'), 'all']);
})
->whereHas('cpaAds.browsers', function ($q) {
$q->whereIn('key', [session('browser'), 'all']);
});
}
]);
Results still include the banners with wrong sizes
Level 67
You're still doing it on the banners
$q->with('cpaAds.cpaBanners')
->whereHas('cpaAds.cpaBanners', function ($q) {
$q->where('size', '=', '728x90');
})
Level 4
@Cronix yeah but if I dont use the with there I have no banners in the result at all, then it’s just a collection of ads.
ads -> hasMany banners banners -> belongTo ads
the other relationships are all belongsToMany
any idea how I can solve this? My head already hurts haha, been on this all day.
Level 4
Okay, after testing, and some more testing, and then testing another day I came up with the solution.
It is totally logical now that I think about it.
Because I am querying through multiple relationships, I have to add ->with() and ->whereHas() to every query scope in Eloquent.
Only by using both, and also in every scope I can have a resulting collection where the parents don't contain either empty results, or results with wrong sizes but instead only the correct sizes.
Now parents are not set if no sizes were found in the distant child and if a distant child has multiple sizes, also only the requested size is included in the result.
Please or to participate in this conversation.