CGPA Calculator with AdonisJS: DB Seeding
Tags: node, javascript, database
Published: 2020-12-05 20:32:12

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](https://github.com/vicradon/cgpa-api) 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

```js
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
```js
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. 
```bash
adonis make:model GradeSystem -m
```
In the grade_system_schema migration file, add these two column difiners.
```js
      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](https://adonisjs.com/docs/4.1/lucid#_createdatcolumn).
```js
  static get createdAtColumn() {
    return null;
  }
  static get updatedAtColumn() {
    return null;
  }
```
## The GradeSystem seeder
Create a seeder using the CLI.
```js
adonis make:seeder GradeSystem
```
Then add replace the content of GradeSystemSeeder.js with this:
```js
"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.
```bash
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.
```bash
adonis make:model Preference -m
```
Add these defines to the migration.
```js
      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.
```js
// inside Preference.js
 gradeSystem() {
    return this.belongsTo("App/Models/GradeSystem");
  }
```
To get the GradeSystem of a Preference, we simply do
```js
await preference.gradeSystem().fetch()
```
## Cumulative model
![cumulative schema.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1606510944880/z1PFi6LL2.png)

We're back to cumulative. Go ahead and create a model and migration file.
```js
adonis make:model Cumulative -m
```
Add these column definers to the cumulative migration file.
```js
      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.
```bash
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.

```js
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.
```js
      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
```js
      const { email, password, grade_system } = request.all();
```
In the rules, add an enum rule for the grade_system
```js
      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.
```js
      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](https://adonisjs.com/docs/4.1/lucid#_findby_findbyorfail). 

Since we have already defined the relationship between Preference and GradeSystem as 
```js
  // App/Models/Preference.js
  gradeSystem() {
    return this.belongsTo("App/Models/GradeSystem");
  }
```
we will use `associate` to bind them.
```js
      // UserController.js register() method
      await preference.gradeSystem().associate(gradeSystemInstance);
```
 `associate` is used on `belongsTo`. Learn more about Lucid relationships from the [docs](https://adonisjs.com/docs/4.1/relationships).

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
```js
  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. 
```js
      await user.preference().save(preference);
      await user.cumulative().save(cumulative);
``` 
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](https://adonisjs.com/docs/4.1/relationships) for more info on relationships.

## Cumulative controller
Create the cumulative controller by running this
```bash
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
```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.
```js
// 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
```js
  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);
    }
  }
```
Register a new user and try it out! 

![Testing the cumulative GET request](https://cdn.hashnode.com/res/hashnode/image/upload/v1606583278805/oqdkalbmJ.png)

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. 
```js
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
```js
      // 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.
```js
      // 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.
![Computed cumulative](https://cdn.hashnode.com/res/hashnode/image/upload/v1606584074013/6HkmMcz8E.png)

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 ✌🏾🧡.