Calculated fields in database - Laravel

Question

What is the best practice to store calculated fields in database.

For example, lets say a table has fields height, weight, bmi

A user enters height weight values and bmi field is automatically filled. How to achieve this with a form.

Formula for bmi $bmi = weight / (height * height)

Tried the following Profile Model

protected $table = 'profiles';

protected $fillable = ['user_id', 'weight', 'height', 'dob', 'age', 'bmi'];

public function user(){
  return $this->belongsTo(User::class, 'user_id');
}

protected static function boot() {
  parent::boot();

  static::saving(function($model){
      $model->bmi = $model->weight / ($model->height * $model->height);
      $model->age = (date('Y') - date('Y',strtotime($model->dob)));
  });
}

Profile Controller

public function store(Request $request)
  {
      $profile = new Profile();
      $profile->weight = $request->get('weight');
      $profile->height = $request->get('height');
      $profile->dob = $request->get('dob');
      $profile->age;
      $profile->bmi;
      $profile->save();
      return back()->with('success', 'Your profile has been updated.');
  }

But im receiving an error

Illuminate \ Database \ QueryException (42S22)
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'weight' in 'field list' (SQL: insert into `users` (`weight`, `height`, `dob`, `bmi`, `age`, `created_by`, `updated_by`, `updated_at`, `created_at`) values (60, 175, 1988-04-03, 0.0019591836734694, 30, 2, 2, 2018-03-08 20:06:02, 2018-03-08 20:06:02))

before inserting the values to the table, you need to calculate the bmi manually and assign it to the `$yourObject->bmi = $calculatedBmi` — KPK, Mar 08 '18 at 17:57
So can i use the same forumla $calculateBmi = $request->weight / ($request->height * $request->height) — , Mar 08 '18 at 18:03

score 6 · Answer 1 · answered Mar 08 '18 at 18:10

6

What is the best practice to store calculated fields in database.

In general, don't. It's redundant - your database already has all the information needed to compute it, so why store redundant data?

Instead, put an accessor on the model:

public function getBmiAttribute() {
    return ($this->weight / ($this->height * $this->height));
}

You can then do $this->bmi to get the computed value.

If you must have it in the database, use an Eloquent event. You'd probably want to hook into the saving event.

answered Mar 08 '18 at 18:10

ceejayoz

176,543
40
303
368

Im currently doing the same via model to display the bmi value in the view. But what im looking for is to save bmi value in database so that i can compare bmi values for users with dates. For example, showing user, your bmi levels were 21.0 last month and 20.0 today. A form is shown to the user on every week. – Mar 08 '18 at 18:24
@SrikanthGopi keep in mind, you're still saving 1 bmi value in the database for each user. This will not help you in comparing month on month values. You would need to have a separate log table for doing this regardless of whether you save the values using an Eloquent model event – Paras Mar 08 '18 at 20:33
@Paras The code that i mentioned in the main question saves multiple profiles for a user. So i can fetch the profile on a weekly basis and compare it with previous profile. I'm doing this for the first time. – Mar 08 '18 at 20:38
1

Oh so the profiles table is like a snapshot copy of the users table at a given point in time. Well you should think about the following: 1) Do you want to copy all the fields (e.g. dob, name that dont change in time)? 2) Do you want users to trigger the saving of profiles or perhaps use task scheduling instead? 3) In your `store` method, you aren't linking the user ID to the profile, maybe you missed it – Paras Mar 08 '18 at 20:44
@Paras Could you provide any reference link or exact search keywords where i could look at few tutorials and learn more about these. I'm new to coding. You make sense. I should not copy dob as it never changes. So users table(with values that dont change) and healthlogs table (with other values those change) should be the right approach?? – Mar 08 '18 at 20:52
1

@SrikanthGopi try searching when to use `normalized` v/s `denormalized` data. Also, try searching good database structures for `logging`. Caching may be premature for your use case but if you're up for learning beyond, go for it – Paras Mar 08 '18 at 20:55
Thanks @Paras ill learn the subjects you gave me and then try something better. – Mar 08 '18 at 21:03

score 5 · Accepted Answer · edited Mar 08 '18 at 18:41

5

You could do this in the boot method of the model:

protected static function boot() {
    parent::boot();

    static::saving(function($model){
        $model->bmi = $model->weight / ($model->height * $model->height);
    }); 
}

edited Mar 08 '18 at 18:41

ceejayoz

176,543
40
303
368

answered Mar 08 '18 at 18:04

Ohgodwhy

49,779
11
80
110

why should we need this in `boot` ? – KPK Mar 08 '18 at 18:08
You could stop yourself from having to handle the calculation in mulitple places in a controller and instead just do it once in a boot method. – Ohgodwhy Mar 08 '18 at 18:12
@KPK That's one of the ways of setting an observer on an Eloquent model. – ceejayoz Mar 08 '18 at 18:13
1

@Ohgodwhy I've edited to be `static::saving` instead of `static::creating`, as you'd expect the BMI to update when the height/weight do. – ceejayoz Mar 08 '18 at 18:42
do i need to mention the model name in place of model i mean static::saving(function($profile){ – Mar 08 '18 at 19:18
1

@SrikanthGopi You can use `static` or you can use the name of the mode. up to you. – Ohgodwhy Mar 08 '18 at 19:53

score 5 · Answer 3 · answered May 24 '19 at 09:47

As of Laravel 5.3 & MySQL 5.7, you can use Column Modifiers virtualAs and storedAs to create a VIRTUAL (evaluated when rows are read) or STORED (evaluated and stored when rows are inserted or updated) generated column for MySQL.

I've made use of virtualAs in the example below...

    Schema::create('results', function (Blueprint $table) {
        $table->bigIncrements('id');
        ...
        $table->time('predicted_time')->nullable()->default(NULL);
        $table->time('handicap')->nullable()->default(NULL);
        $table->time('finish_time')->nullable()->default(NULL);
        $table->time('actual_time')->virtualAs('TIMEDIFF(finish_time, handicap)');
        $table->time('offset')->virtualAs('TIMEDIFF(actual_time, predicted_time)');
        ...        
    });

score 2 · Answer 4 · answered Mar 08 '18 at 20:29

What is the best practice to store calculated fields in database.

It depends on your use case. If you're using a relational database, and your use case does not involve big data (in terms of volume, variety or velocity), the best practice is to not store calculated fields and calculate them on the fly.

If you're using a noSQL database (such as MongoDB, which is supported by Laravel) or Hadoop, the best practice is to store calculated fields to avoid computational time.

In A Nutshell

It's a tradeoff between time complexity and space/storage complexity. For big data / noSQL systems, store calculated fields especially if they are computationally complex. For a relational database, calculate them on the fly and keep your data non-redundant

Laravel Solution for RDBMS:

Use accessors like so:

public function getBmiAttribute($value)
{
    return ($this->weight / ($this->height * $this->height));
}

Laravel Solution for NoSql

Use model events like so:

protected static function boot() {
    parent::boot();

    static::saving(function($model){
        $model->bmi = $model->weight / ($model->height * $model->height);
    }); 
}

score 2 · Answer 5 · answered Feb 02 '19 at 20:49

If your DBMS supports computed columns (aka generated columns), you might consider utilizing them.

Highlights:

Computed columns don't persist the data to the database (or if they do technically they'll be managed by the DBMS), so you're not duplicating any data or making anything redundant
The calculation is then available outside of the application's code base for anything to use. For example, if there is a need to develop reporting with raw SQL queries then the calculation will be available there.
Eloquent (Laravel's default ORM) will pick up the column without you necessarily needing to define it anywhere.

You could execute code to create it during a migration. For example, I had a use case where I wanted to simplify determining if something was currently published and I did it like this:

Schema::table('article', function (Blueprint $table) {
    $table->dateTime('published_at')->nullable();
    $table->dateTime('unpublished_at')->nullable();
});

$sql = "ALTER TABLE article ADD is_published AS CAST(CASE WHEN GETUTCDATE() BETWEEN ISNULL(published_at, '2050-01-01') AND ISNULL(unpublished_at, '2050-01-01') THEN 1 ELSE 0 END AS BIT);";
DB::connection()->getPdo()->exec($sql);

Then after retrieving a record from the database I can determine if it's published by simply checking...

if ($article->is_published) ...

And if I want to query it from the database I can easily do...

SELECT * FROM article WHERE is_published = 1

Note, this syntax is for T-SQL since the RDBMS for my project is MS SQL Server. However, other popular DBMS have similar features.

MS SQL Server: Computed Columns

ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.35);
MySQL: Generated Columns

ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;
Oracle: Virtual Columns

ALTER TABLE emp2 ADD (income AS (salary + (salary*commission_pct)));

PostgreSQL doesn't seem to support it (yet), but this SO answer provides a decent workaround.

score 0 · Answer 6 · answered Jan 23 '23 at 11:46

another way to make this making a computed column in the migration this working with all DB:-

 $table->computed ('tax','price * 0.27');

but this working with MySQL DB:-

 $table->integer('tax')->virtualAs('price * 0.27')->nullable();
 $table->integer('discount')->storedAs('price - 100')->nullable();

Calculated fields in database - Laravel

6 Answers6