Hi all,
Been bouncing back and forth with this so thought I might ask here.
Basically, let's say I have a transactions table & model. Each transaction can have multiple attributes. These attributes are to be stored in an attribute table.
Transaction model will have a hasMany attributes relationship. Attributes will have a belongsTo attribute linking back to the transaction.
My issue is that these attributes can be primarily strings and doubles. How would I go about storing these in the DB and use SQLs built in functionalities. Eg: if I save all values as string, how do I go about utilizing the SUM, AVG,MAX functions in SQL?
Would casting be of any help here? I doubt it.
Just wanted to get your thoughts on how you would handle it.
One idea is to have either 2 separate tables (1 for strings, 1 for doubles), or 2 columns in the same table for each data type. Seems like anti-pattern.
Appreciate any guidance on this.
By casting. Just example:
$amt = (float) $array[$i][3];
$namt = number_format($amt, 2, '.', '');
if ($namt < 0) {
$wd = $namt * -1;
$dep = 0;
} else {
$wd = 0;
$dep = $namt;
}
Look up mutators an assessors in the laravel documentation also.
@jlrdw are you suggesting that I store another column to store the data type and create a custom cast? We wouldn’t be able to use the native MySQL functions right? Eg: number stored as string in db until laravel casts it?
@elabuwa why not make two columns? On insert check the value type and insert it on the correct column, when retrieving make an attribute to check both columns and return the one with a value:
public function getValueAttribute(): string|float|int|null
{
return $this->double_value ?? $this->string_value;
}
Please or to participate in this conversation.