I have an Affiliate model with a AffiliateSummaryStat statistic model which contains aggregated totals for each hour of the day and linked by affiliate_id. I need to be able to return a single AffiliateSummaryStat but with the sum of each model. How can I achieve this?
Here’s my relationship right now, this is my Affiliate model:
<?php
namespace AppModels;
use IlluminateDatabaseEloquentFactoriesHasFactory;
use IlluminateDatabaseEloquentModel;
use IlluminateDatabaseEloquentSoftDeletes;
use IlluminateSupportFacadesCache;
class Affiliate extends Model
{
use HasFactory, SoftDeletes;
/**
* The table associated with the model.
*
* @var string
*/
protected $table = 'affiliates';
/**
* The attributes that should be cast.
*
* @var array
*/
protected $casts = [
'allow_submission' => 'boolean',
'is_favourited' => 'boolean',
'is_default' => 'boolean',
'is_enabled' => 'boolean',
'last_used_at' => 'datetime',
];
/**
* The attributes that should be hidden for serialization.
*
* @var array<int, string>
*/
protected $hidden = [
'api_key',
];
/**
* The accessors to append to the model's array form.
*
* @var array
*/
protected $appends = [
'is_deleting',
];
/**
* Determine if we're editing the model
*/
public function getIsDeletingAttribute(): bool
{
return false;
}
/**
* Get the affiliate summary stats
*/
public function affiliate_summary_stats()
{
return $this->hasMany(AffiliateSummaryStat::class);
}
/**
* Get the commissions for the affiliate
*/
public function commissions()
{
return $this->hasMany(Commission::class);
}
/**
* Get the affiliate products for this model.
*/
public function products()
{
return $this->hasMany(AffiliateProduct::class);
}
/**
* Get the splits for this affiliate
*/
public function splits()
{
return $this->hasMany(AffiliateSplit::class);
}
/**
* Get the applications for this affiliate
*/
public function applications()
{
return $this->hasMany(Application::class);
}
/**
* Get the company that owns the model.
*/
public function company()
{
return $this->belongsTo(Company::class);
}
/**
* Get the user that owns the model.
*/
public function user()
{
return $this->belongsTo(User::class);
}
/**
* Bootstrap any application services.
*/
public static function boot()
{
parent::boot();
// on create
static::created(function ($model) {
Cache::tags('affiliates')->flush();
});
// on update
static::updated(function ($model) {
Cache::tags('affiliates')->flush();
});
}
}
And is used in my controller:
$report = Affiliate::with([
'affiliate_summary_stats'
])->select([
'id', 'aff_id', 'description', 'last_used_at'
])->get();
MY current output:
{
"model": [
{
"id": 1,
"aff_id": "aff107",
"description": "Default system affiliate",
"last_used_at": null,
"is_deleting": false,
"affiliate_summary_stats": [
{
"id": 1,
"affiliate_id": 1,
"brand": null,
"campaign": "B",
"total_sessions": 2,
"total_submits": 0,
"total_leads": 0,
"total_accepted": 0,
"total_declined": 0,
"total_redirected": 0,
"logged_at": "2023-03-21T08:00:00.000000Z",
"created_at": "2023-03-21T08:47:33.000000Z",
"updated_at": "2023-03-21T08:47:33.000000Z",
"total_sessions_formatted": "2",
"total_submits_formatted": "0",
"total_leads_formatted": "0",
"total_accepted_formatted": "0",
"total_declined_formatted": "0",
"total_redirected_formatted": "0",
"conversion_rate": 0,
"conversion_rate_formatted": "0",
"conversion_rate_formatted_with_symbol": "0%"
},
{
"id": 2,
"affiliate_id": 1,
"brand": null,
"campaign": "A",
"total_sessions": 2,
"total_submits": 0,
"total_leads": 0,
"total_accepted": 0,
"total_declined": 0,
"total_redirected": 0,
"logged_at": "2023-03-21T08:00:00.000000Z",
"created_at": "2023-03-21T08:47:36.000000Z",
"updated_at": "2023-03-21T08:47:36.000000Z",
"total_sessions_formatted": "2",
"total_submits_formatted": "0",
"total_leads_formatted": "0",
"total_accepted_formatted": "0",
"total_declined_formatted": "0",
"total_redirected_formatted": "0",
"conversion_rate": 0,
"conversion_rate_formatted": "0",
"conversion_rate_formatted_with_symbol": "0%"
},
{
"id": 3,
"affiliate_id": 1,
"brand": null,
"campaign": null,
"total_sessions": 3,
"total_submits": 3,
"total_leads": 3,
"total_accepted": 3,
"total_declined": 0,
"total_redirected": 0,
"logged_at": "2023-03-21T08:00:00.000000Z",
"created_at": "2023-03-21T08:47:39.000000Z",
"updated_at": "2023-03-21T08:56:04.000000Z",
"total_sessions_formatted": "3",
"total_submits_formatted": "3",
"total_leads_formatted": "3",
"total_accepted_formatted": "3",
"total_declined_formatted": "0",
"total_redirected_formatted": "0",
"conversion_rate": 100,
"conversion_rate_formatted": "100.00",
"conversion_rate_formatted_with_symbol": "100.00%"
},
{
"id": 5,
"affiliate_id": 1,
"brand": null,
"campaign": "A",
"total_sessions": 2,
"total_submits": 0,
"total_leads": 0,
"total_accepted": 0,
"total_declined": 0,
"total_redirected": 0,
"logged_at": "2023-03-21T08:00:00.000000Z",
"created_at": "2023-03-21T08:47:36.000000Z",
"updated_at": "2023-03-21T08:47:36.000000Z",
"total_sessions_formatted": "2",
"total_submits_formatted": "0",
"total_leads_formatted": "0",
"total_accepted_formatted": "0",
"total_declined_formatted": "0",
"total_redirected_formatted": "0",
"conversion_rate": 0,
"conversion_rate_formatted": "0",
"conversion_rate_formatted_with_symbol": "0%"
}
]
},
{
"id": 2,
"aff_id": "aff4000",
"description": "Old",
"last_used_at": null,
"is_deleting": false,
"affiliate_summary_stats": []
}
]
}