Styjun

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('AppUser');


public function poas()
 return $this->hasMany('AppPOA', '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('AppClaim', 'claim_id', 'id');


public function user()
 return $this->belongsTo('AppUser', '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;