CGPA Calculator with AdonisJS: DB Seeding

Hey there, welcome to the 4th tutorial in the series. In the previous tutorial, we created CRUD methods for the Course model. If you will like to skip the previous steps, clone the repo and checkout to the courses-crud-and-relationships branch, then code along.

Computing the cumulative

The goal of this API is to compute a student's CGPA. There are several different systems for computing CGPA. For this tutorial, we will stick with the 4 and 5 point systems.

4 Point System

The 4 point system follows this mapping

Grade	Points
A	4
B	3
C	2
D	1
F	0

5 Point System

And the that of the 5 point system is:

Grade	Points
A	5
B	4
C	3
D	2
F	0

Now, if we were to compute a student's CGPA given these details

const courses = [
  {
    code: "MTH 304",
    grade: "B",
    credit_load: 4,
  },
  {
    code: "GSW 102",
    grade: "A",
    credit_load: 3,
  },
  {
    code: "VEY 201",
    grade: "D",
    credit_load: 1,
  },
  {
    code: "WOR 423",
    grade: "F",
    credit_load: 2,
  },
];

We will follow this algorithm

let total_grade_point = 0
let total_credit_load = 0
for (course of courses){
   total_grade_point += course.grade * course.credit_load
   total_credit_load += course.credit_load
}
const cgpa = total_grade_point/total_credit_load

If you notice total_grade_point += course.grade * course.credit_load has a multiplication of a string and number. We need a way to convert. Like a lookup table. We can either save this table in our code or database. We will go with the later. This is where seeding comes in. We will seed the lookup table with data. We will call this lookup table grade_systems.

The GradeSystem model

Start by creating the model and its migration.

adonis make:model GradeSystem -m

In the grade_system_schema migration file, add these two column difiners.

      table.integer("point").notNullable();
      ["A", "B", "C", "D", "E", "F"].map((grade) => {
        table.integer(grade).unsigned();
      });

You can remove the table.timestamps() definer to make your table cleaner. If you do remove it, add these static getters to your GradeSystem model. Learn more from the docs.

  static get createdAtColumn() {
    return null;
  }
  static get updatedAtColumn() {
    return null;
  }

The GradeSystem seeder

Create a seeder using the CLI.

adonis make:seeder GradeSystem

Then add replace the content of GradeSystemSeeder.js with this:

"use strict";

const GradeSystem = use("App/Models/GradeSystem");

class GradeSystemSeeder {
  async run() {
    const points = [4, 5];
    for (let point of points) {
      const system = new GradeSystem();
      system.point = point;

      ["A", "B", "C", "D"].map((grade, index) => {
        system[grade] = point - index;
      });

      system["F"] = 0;

      await system.save();
    }
  }
}

module.exports = GradeSystemSeeder;

Now, run the pending migration and simultaneously seed the database.

adonis migration:run --seed

User preferences

Since we won't know by default the grade_system of our users, we need to give them the option of changing it. We will do that in preferences. First, we will create a model and migration.

adonis make:model Preference -m

Add these defines to the migration.

      table
        .integer("grade_system_id")
        .unsigned()
        .references("id")
        .inTable("grade_systems")
        .onUpdate("CASCADE")
        .onDelete("SET NULL");
      table
        .integer("user_id")
        .unsigned()
        .nullable()
        .references("id")
        .inTable("users")
        .onUpdate("CASCADE")
        .onDelete("CASCADE");

They are basically foreign keys that point to the grade_systems and users tables. The Preference and GradeSystem models share a 1:1 relationship. It makes more sense to say that a Preference maps to a GradeSystem. This means we will define the relationship in the Preference model.

// inside Preference.js
 gradeSystem() {
    return this.belongsTo("App/Models/GradeSystem");
  }

To get the GradeSystem of a Preference, we simply do

await preference.gradeSystem().fetch()

Cumulative model

We're back to cumulative. Go ahead and create a model and migration file.

adonis make:model Cumulative -m

Add these column definers to the cumulative migration file.

      table.integer("credit_load").unsigned();
      table.integer("grade_point").unsigned();
      table.decimal("grade_point_average", 20, 2).unsigned();
      table
        .integer("user_id")
        .unsigned()
        .nullable()
        .unique()
        .references("id")
        .inTable("users")
        .onUpdate("CASCADE")
        .onDelete("CASCADE");

We set a 2 decimal places precision to the grade_point_average with a max number length of 20 characters. Run the migration.

adonis migration:run

Now that we have the preferences and cumulatives tables set up, we can handle the relationships. When a user registers, we want to initialize a row on the cumulative and preferences tables. To do these, we will create the models and associate them with the User model. First, require these models.

const Preference = use("App/Models/Preference");
const GradeSystem = use("App/Models/GradeSystem");

Then create a new instance of the each model in the register method of UserController.

      const preference = new Preference();
      const cumulative = await Cumulative.create({
        credit_load: 0,
        grade_point: 0,
        grade_point_average: 0,
      });

It makes sense to collect the user's preferred grading system during registration. So, let's make that an optional field. Add grade_system to your request.all() so it becomes this

      const { email, password, grade_system } = request.all();

In the rules, add an enum rule for the grade_system

      const rules = {
        email: "required|email|unique:users,email",
        password: "required",
        grade_system: "in:4,5",
      };

Save the required grade_system instance to a variable gradeSystemInstance. If the grade system isn't provided, we set it as 5.

      const gradeSystemInstance = await GradeSystem.findBy(
        "point",
        grade_system | "5"
      );

Notice we didn't use query() to find this instance. findBy is a static method of a Lucid model. Learn more from the docs.

Since we have already defined the relationship between Preference and GradeSystem as

  // App/Models/Preference.js
  gradeSystem() {
    return this.belongsTo("App/Models/GradeSystem");
  }

we will use associate to bind them.

      // UserController.js register() method
      await preference.gradeSystem().associate(gradeSystemInstance);

associate is used on belongsTo. Learn more about Lucid relationships from the docs.

The last thing left is to tie the cumulative and preference instances to the user. Since they are both 1:1 relationships, we will use hasOne to define them. Inside app/Models/User.js, add these methods

  cumulative() {
    return this.hasOne("App/Models/Cumulative");
  }
  preference() {
    return this.hasOne("App/Models/Preference");
  }

Now, in the register method, we will use save instead of associate to register the foreign keys.

      await user.preference().save(preference);
      await user.cumulative().save(cumulative);

The rule of thumb is to use save with

hasOne
hasMany and use associate when using the inverse of hasOne i.e. belongsTo. Please refer to the docs for more info on relationships.

Cumulative controller

Create the cumulative controller by running this

adonis make:controller Cumulative --type=http

We need two methods. One for returning the computed CGPA and another for computing the CGPA. We will use a GET request for the first and a PATCH request for the second. Add these routes to routes.js

 Route.get("cumulative", "CumulativeController.show").middleware(["auth"]);
 Route.patch("cumulative", "CumulativeController.update").middleware(["auth"]);

First things first for the controller, we import the cumulative model. We also add the course and preference models because we'll be making use of them.

// CumulativeController.js
const Cumulative = use("App/Models/Cumulative");
const Course = use("App/Models/Course");
const Preference = use("App/Models/Preference");

Now, for the GET request, we simply return the cumulative. So our method will be

  async show({ auth, response }) {
    try {
      const user = await auth.user;
      const cumulative = await Cumulative.findBy("user_id", user.id);

      return response.status(200).send(cumulative);
    } catch (error) {
      return response.status(500).send(error);
    }
  }

The PATCH request is more involved. We will first calculate the cumulative before saving and returning it. We start of by finding the user's cumulative and grading system.

async update({ auth, response }) {
    try {
      const user = await auth.user;
      const cumulative = await Cumulative.findBy("user_id", user.id);
      const preference = await Preference.findBy("user_id", user.id);
      const grade_system = await preference.gradeSystem().fetch();

    } catch (error) {
      console.log(error);
      return response.status(500).send(error);
    }
  }

Then we query the user's courses

      // update method: CumulativeController.js
      const raw_courses = await Course.query()
        .where("user_id", user.id)
        .fetch();
      const courses = raw_courses.toJSON();

Afterwards, we compute the total_credit_load and total_grade_point.

      // update method: CumulativeController.js
     const total_credit_load = courses.reduce((accumulator, course) => {
        return accumulator + course.credit_load;
      }, 0);
      const total_grade_point = courses.reduce((accumulator, course) => {
        return accumulator + course.credit_load * grade_system[course.grade];
      }, 0);

Finally, we replace the existing cumulative values with the newly computed values, persist it to the DB and return the cumulative.

      // update method: CumulativeController.js
      cumulative.credit_load = total_credit_load;
      cumulative.grade_point = total_grade_point;
      cumulative.grade_point_average = Number(
        (total_grade_point / total_credit_load).toFixed(2)
      );

      await cumulative.save();
      return response.status(200).send(cumulative);

Here's the response of the PATCH request.

That's it. You did it. We are done! Congrats on making it this far. Are we missing something? How can we be confident that all the code we've written so far won't break in production 🤔? There's only one way to be sure. We write tests!