## DEV Community 👩‍💻👨‍💻 # 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

A 4
B 3
C 2
D 1
F 0

### 5 Point System

And the that of the 5 point system is:

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",
},
{
code: "GSW 102",
},
{
code: "VEY 201",
},
{
code: "WOR 423",
},
];
``````

``````let total_grade_point = 0
for (course of courses){
}
``````

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.

Start by creating the model and its migration.

``````adonis make:model GradeSystem -m
``````

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

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;
}
``````

Create a seeder using the CLI.

``````adonis make:seeder GradeSystem
``````

``````"use strict";

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

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

system["F"] = 0;

await system.save();
}
}
}

``````

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
.unsigned()
.references("id")
.onDelete("SET NULL");
table
.integer("user_id")
.unsigned()
.nullable()
.references("id")
.inTable("users")
``````

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
}
``````

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("user_id")
.unsigned()
.nullable()
.unique()
.references("id")
.inTable("users")
``````

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");
``````

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

``````      const preference = new Preference();
const cumulative = await Cumulative.create({
});
``````

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();
``````

``````      const rules = {
email: "required|email|unique:users,email",
};
``````

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",
);
``````

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
}
``````

we will use `associate` to bind them.

``````      // UserController.js register() method
``````

`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

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
``````

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);
}
}
``````

Register a new user and try it out! 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);

} 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) => {
}, 0);
const total_grade_point = courses.reduce((accumulator, course) => {
}, 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
);

await cumulative.save();
return response.status(200).send(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 ✌🏾🧡. 