Calculations in controller queries

[TJMusiitwa]

I have a case where two columns have data that I would like to add to generate the result entry for a third column. Any idea how I might go about this in controller with say my @Operation.put( ) and/or my seed function in the migrations file?

Example of model where male + female = totalAgeGroup

class AgeGender extends ManagedObject<_AgeGender> implements _AgeGender {
  @override
  void willUpdate() {
    updatedDate = DateTime.now().toLocal();
  }
}

class _AgeGender {
  @Column(primaryKey: true)
  int id;

  String ageGroup;

  int male;
  int female;

  int totalAgeGroup;

  DateTime updatedDate;
}

Inside the seed function

    final seedAgeGroup = [
      {
        'ageGroup': "20-29",
        'male': 25,
        'female': 18,
        'totalAgeGroup': 43,
        'lastUpdated': DateTime.now().toLocal(),
      }
    ];
    for (final ageGroupItem in seedAgeGroup ) {
      await database.store.execute(
          "INSERT INTO _AgeGender(ageGroup, male, female, totalAgeGroup, lastUpdated) VALUES (@ageGroup, @male, @female, @totalAgeGroup, @lastUpdated)",
          substitutionValues: {
            "ageGroup": ageGroupItem ['ageGroup'],
            "male": ageGroupItem ['male'],
            "female": ageGroupItem ['female'],
            "totalAgeGroup": ageGroupItem ['totalAgeGroup'],
            "lastUpdated": ageGroupItem ['lastUpdated'],
          });

}

[Reductions]

There are 2 ways to do that in with a insert from a controller and one more that does in the database.

Here is the first one:

class AgeGender extends ManagedObject<_AgeGender> implements _AgeGender {
  @override
  void willCreate() {
    calcTotal();
  }
  @override
  void willUpdate() {
    calcTotal();
    updatedDate = DateTime.now().toLocal();
  }
  
  void calcTotal() {
    totalAgeGroup = male + female;
  }
}
class _AgeGender {
  @Column(primaryKey: true)
  int id;
  String ageGroup;
  int male;
  int female;
  int totalAgeGroup;
  DateTime updatedDate;
}

[Reductions]

If you don't need it in the database you could do something like this:

class AgeGender extends ManagedObject<_AgeGender> implements _AgeGender {
  @Serialize()
  int get totalAgeGroup => male + female;

  @override
  void willUpdate() {
    updatedDate = DateTime.now().toLocal();
  }
}
class _AgeGender {
  @Column(primaryKey: true)
  int id;
  String ageGroup;
  int male;
  int female;
  DateTime updatedDate;
}

[Reductions]

A 3th option is to create a trigger on the database that does that for you.

CREATE FUNCTION calc_total_age_group()
RETURNS trigger AS
$$
BEGIN
  NEW.totalAgeGroup = NEW.male + NEW.female;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER calc_total_trigger
  BEFORE INSERT OR UPDATE ON _agegender
  FOR EACH ROW EXECUTE PROCEDURE calc_total_age_group();

You can create the procedure and the trigger in the migration that create the table. Using this approach no matter how you insert the data you will always have a consistent column for totalAgeGroup. And it will work both with in the controller and in the seed.

[TJMusiitwa]

Thank you so much for the quick reply. I'll just opt for the first option in the hopes of keeping it simple. But in future complex projects I'll try out the other two.
Many thanks.👍🏾