Arithmetic calculations on created columns?
How does one do arithmetic calculations (e.g. sum product) on calculated columns that do not yet exist in DB? E.g. if you have a model claims
and it belongsToMany passengers
(passengers technically stored as users).
Neither model will keep a count of how many passengers there is per claim, the value is stored in a pivot table claim_user
.
I aim targeting to do an SQL calculation along the line of $valuePerPassenger = claims.value / passengers_count
where conditions of join are met.
Target query
$query = Claim::query();
$query->addSelect('id' , 'commission');
$query->withCount('passengers', 'poasSigned');
$query->addSelect(DB::raw(sum('claims.commission / passengers_count * poas_signed_count as expectedClaimValue');
$result = $query->get;
// $result = $query->sum('expectedClaimValue'); <-- Bonus if this works
Please note that Laravel generates passengers_count
and poas_signed_count
(i.e. powers_of_attorney_signed) and attaches to the eloquent record via the withCount
method. But they do not exist before the query.
Error message: Column not found: 1054
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'claims.commission / passengers_count * poas_signed_count' in 'field list' (SQL: select `claims`.*, (select count(*) from `users` inner join `claim_user` on `users`.`id` = `claim_user`.`user_id` where `claims`.`id` = `claim_user`.`claim_id` and `users`.`deleted_at` is null) as `passengers_count`, (select count(*) from `powers_of_attorney` where `claims`.`id` = `powers_of_attorney`.`claim_id` and `powers_of_attorney`.`deleted_at` is null) as `poas_count`, `id`, `commission`, (select count(*) from `users` inner join `claim_user` on `users`.`id` = `claim_user`.`user_id` where `claims`.`id` = `claim_user`.`claim_id` and `users`.`deleted_at` is null) as `passengers_count`, (select count(*) from `powers_of_attorney` where `claims`.`id` = `powers_of_attorney`.`claim_id` and `status` = closed and `powers_of_attorney`.`deleted_at` is null) as `poas_signed_count`, `claims`.`commission / passengers_count * poas_signed_count` as `expectedClaimValue` from `claims` where `claims`.`deleted_at` is null)
Workaround
Created a workaround using chunk and looping through the records as such:
Claim::select('id', 'commission')
->withCount('passengers', 'poasSigned')
->chunk(100, function ($claims) use (&$data) {
foreach ($claims as $claim){
$data['expectedClaimValue'] += $claim->commission / $claim->passengers_count * $claim->poas_signed_count;
}
});
But obviously that workaround is much slower as it relies on creating collections to calculate the results. I am hoping to do this straight in MySQL query.
I have found many answers (e.g. arithmetic operations in query builder laravel) on how to do the raw query if the columns already exist on the related model/ DB record. But in case the column is a calculation based on pivot/relationship - how does one make that work with DB::raw?
Thank you for the help!
Claim Model
DB structure
-id
-commission
-...
public function passengers(){
return $this->belongsToMany('App\User');
}
public function poas(){
return $this->hasMany('App\POA', 'claim_id', 'id');
}
public function poasSigned(){
return $this->poas()->signed();
}
protected function getPassengersCountAttribute($value){
return $value ?? $this->passengers_count = $this->passengers()->count();
}
protected function getPoasCountAttribute($value){
return $value ?? $this->poas_count = $this->poas()->count();
}
protected function getPoasSignedCountAttribute($value){
return $value ?? $this->poasSigned_count = $this->poasSigned()->count();
}
User Model
DB structure
-id
-name
-...
Passenger Model (table = 'claim_user')
DB structure
-id
-claim_id
-user_id
PowersOfAttorney Model
DB structure
-id
-status
-claim_id
-user_id
public function claim(){
return $this->belongsTo('App\Claim', 'claim_id', 'id');
}
public function user(){
return $this->belongsTo('App\User', 'user_id', 'id');
}
public function scopeSigned($query){
return $query->where('status', '=', 'closed');
}
public function getSignedAttribute(){
// Verify POA signed (closed) AND not deleted
return $this->status == 'closed' && $this->deleted_at == null;
}