DEV Community

Cover image for CGPA Calculator with AdonisJS: DB Seeding
Osinachi Chukwujama
Osinachi Chukwujama

Posted on

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,
  },
];
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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();
      });
Enter fullscreen mode Exit fullscreen mode

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;
  }
Enter fullscreen mode Exit fullscreen mode

The GradeSystem seeder

Create a seeder using the CLI.

adonis make:seeder GradeSystem
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

adonis migration:run --seed
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

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");
  }
Enter fullscreen mode Exit fullscreen mode

To get the GradeSystem of a Preference, we simply do

await preference.gradeSystem().fetch()
Enter fullscreen mode Exit fullscreen mode

Cumulative model

cumulative schema.png

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

adonis make:model Cumulative -m
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

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,
      });
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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",
      };
Enter fullscreen mode Exit fullscreen mode

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"
      );
Enter fullscreen mode Exit fullscreen mode

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");
  }
Enter fullscreen mode Exit fullscreen mode

we will use associate to bind them.

      // UserController.js register() method
      await preference.gradeSystem().associate(gradeSystemInstance);
Enter fullscreen mode Exit fullscreen mode

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");
  }
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

The rule of thumb is to use save with

  1. hasOne
  2. 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
Enter fullscreen mode Exit fullscreen mode

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"]);
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

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);
    }
  }
Enter fullscreen mode Exit fullscreen mode

Register a new user and try it out!

Testing the cumulative GET request

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);
    }
  }
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

Here's the response of the PATCH request.
Computed cumulative

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!

Recap

Before we go on to write tests, let's review what we learned in this tutorial.

  1. How to create a seeder
  2. How to run migrations and seed simultaneously.

Thank you for following along. In the next tutorial, we will write functional tests for our APIs. Thank you for following along. Adios ✌🏾🧡.

Top comments (0)