read24 - Admin UI - Managing Teachers (Part 2)

August 17, 2020

Roger Ngo

Today, I’ll work on creating some pages to get some users and teachers into the system! There will be a lot of changes, and I think by the end of it all, there will be lots of functionality, but there will be a lot of code added which will prompt refactoring of the codebase in the coming days. Refactoring is being considered, but will not be performed today.

teacher_classroom‌

First thing’s first, I will need to update the database to have teacher_classroom table. Here is the schema definition:

create table teacher_classroom (
    `id` bigint primary key auto_increment,
    `dateCreated` bigint null,
    `dateUpdated` bigint null,
    `dateDeleted` bigint not null default 0,
    `teacher_id` bigint,
    `classroom_id` bigint
);

As you can see, it is just a simple JOIN table. I will need to update the tables.ts file to contain the new mapping to the table too.

export const TableMapping = {
    ...,
    teacher_classroom: 'teacher_classroom',
    ...
};

Now, I will need a resource class for teacher_classroom. This class will be called TeacherClassroom, and will contain the basic table properties. The additional methods will also help in finding data:‌

You may have notice that only 1 teacher can be assigned to a classroom, but many classrooms can be assigned to a teacher. Therefore, it can be possible to have more than one result retrieved from findClassroomByTeacherId. For now, let’s just assume one result is returned. This is only temporary!

import { DataType } from "../db/types";
import { BaseResource } from "../db/base_resource";
import { DatabaseConnector } from "../config";
import { Classroom } from "./classroom";
import { Teacher } from "./teacher";

export interface TeacherClassroomType extends DataType {
    teacherId: number;
    classroomId: number;
};

export class TeacherClassroom 
    extends BaseResource 
    implements TeacherClassroomType {
    
    public teacherId: number;
    public classroomId: number;

    constructor(initialProperties?: {[property: string]: any}) {
        super('teacher_classroom');

        if (initialProperties) {
            this.teacherId = initialProperties.teacherId;
            this.classroomId = initialProperties.classroomId;
        }
    }

    public static async findByTeacherAndClassroomId(teacherId: number, classroomId: number) {
        const teacherClassrooms = await DatabaseConnector.select('teacher_classroom', {
            filters: [{
                column: 'classroomId',
                value: classroomId
            }, {
                column: 'teacherId',
                value: teacherId
            }]
        });

        if (teacherClassrooms.length === 0)
            return undefined;

        return await new TeacherClassroom().load(teacherClassrooms[0].id);
    }

    public static async findTeacherByClassroomId(classroomId: number) {
        const teacherIds = await DatabaseConnector.select('teacher_classroom', {
            filters: [{
                column: 'classroomId',
                value: classroomId
            }]
        });

        const teachers = await Promise.all(teacherIds.map(async tc => await new Teacher().load(tc.teacherId)));

        if (teachers.length === 0)
            return undefined;

        return teachers[0];
    }

    public static async findClassroomByTeacherId(teacherId: number) {
        const classroomIds = await DatabaseConnector.select('teacher_classroom', {
            filters: [{
                column: 'teacherId',
                value: teacherId
            }]
        });

        const classrooms = await Promise.all(classroomIds.map(async tc => await new Classroom().load(tc.classroomId)));

        if (classrooms.length === 0)
            return undefined;
    
        return classrooms[0];
    }
}

types.ts will also need to be updated to export TeacherClassroomType properties too:

export type DataRow = DataType & 
    QuizTokenType & 
    BookType & 
    ClassroomType & 
    QuestionType & 
    QuizQuestionType & 
    RatingType & 
    StudentType & 
    StudentAnswerType & 
    UserType & 
    TeacherClassroomType;

From here on, I am set with being able to access the teacher_classroom table.

NOT IN-clause

Next, the DatabaseConnector should be updated to be able to support SELECT statements with IN-clauses. Amazingly, I’m not sure why this did not occur to me before, but it was not built in! Now is a good time for me to do so.

While I am performing this task, I should also support NOT IN too. Specifying to be a NOT IN clause can be done through InOption with the not switch.

export interface InOption {
    column: string;
    value: string[]|number[];
    not?: boolean;
}

Then the SQL connector has a change to construct IN and NOT IN clauses based on the InOption.

if (opts.in) {
    const valuesList = (opts.in.value as any[]).map(v => connection.escape(v)).join(', ');
    queryString += ` AND ${opts.in.column} ${opts.in.not ? 'NOT IN' : 'IN'} (${valuesList})`;
}

Something similar is also done for the delete method in sql.ts.

Now, when it comes to the in-memory database implemented in memory.ts, the IN-clause will be solved first before filtering.

// solve in-clause first
if (opts.in) {
    filteredData = filteredData.filter(r => {
        if (opts.in.not)
            return !((opts.in.value as any[]).includes(r[opts.in.column]));
        else  
            return (opts.in.value as any[]).includes(r[opts.in.column]);
    });
}

And in delete:

if (opts.in) {
    let deletedInEntries = [...allData].filter(r => {
        const val = r[opts.in.column];
        return opts.in.not 
            ? !((opts.in.value as any).includes(val)) 
            : (opts.in.value as any[]).includes(val)
    });
    
    combined.push(...deletedInEntries);
}

I also updated my unit tests for the memory.ts file to cover these changes.

Adding a Teacher‌

A teacher is a user, but there isn’t a way to add users in the system right now. So, a new page should be created to add a user. This page will be called AddUser. How creative!‌

I won’t be discussing exact implementation details here. Feels like I’ve made enough of these types of pages at this point, but here is a summary.

The page looks like all the other pages I have been creating: “teacher” related things associated with it. For now, it is just the name. Here is the high level overview of the page.

Once a user is designated as a teacher, they no longer become available to be associated as another teacher. So it is a 1-to-1 correspondence. An endpoint can be exposed to query for the list of available users, GET /admin/user/available. Only the user ID and username for each user is needed, and I should not be worried to expose more data than needed.

app.get ('/admin/user/available', async (req, res) => {
    const users = await User.listUsersWhoAreAvailable();

    return res.status(200).json(users.map(u => ({
        username: u.username,
        id: u.id
    })));
});

listUsersWhoAreAvailable is implemented as:

public static async listUsersWhoAreAvailable() {
    const usersAssignedToTeachers = await Teacher.listAllUsersAsTeachers();

    const unavailableIds = usersAssignedToTeachers.map(t => t.id);
    const users = await DatabaseConnector.select('users', {
        in: {
            not: true,
            column: 'id',
            value: unavailableIds
        }
    });

    return Promise.all(users.map(async u => await new User().load(u.id)));
}

Notice that a query is built with a NOT IN clause with a list of user IDs which have already been assigned to teachers. Within the Teachers resource class, the method listAllTeachersAsTeachers will select just the userId from all rows in the teacher table.

public static async listAllUsersAsTeachers() {
    const results = await DatabaseConnector.select('teachers', {
        columns: ['id', 'userId']
    });

    const users = results.map(async r => await new User().load(r.userId));

    return Promise.all(users);
}

When the AddTeacher page is initially loaded, then a fetch call to the API endpoint is made to obtain the data:

useEffect(() => {
    const setupAvailableUsers = async () => {
        const users = await (await fetch(`${API_HOST}/admin/user/available`)).json();

        console.log(users);
        if (users.length === 0)
            setAvailableUsers([]);
        else
            setAvailableUsers(users);
    };

    setupAvailableUsers();
}, []);

Then a state variable to keep track of the current selected user ID from the SelectBox is referenced along with all the form data when the form is submitted to the server to create the teacher:

const response = await (await fetch(`${API_HOST}/admin/teacher`, {
    method: 'POST',
    headers: {
        'Content-Type': 'application/json'
    },
    body: JSON.stringify({
        firstName,
        middleName,
        lastName,
        userId
    })
})).json();

Listing Teachers

Next it is the ListTeachers page.

The page is just like the other two pages to list classrooms, and users. When the page is initially loaded, a fetch is made to the GET /admin/teacher/all/pages/:page endpoint to retrieve a subset of the data along with pagination information.

useEffect(() => {
    const getData = async () => {
        const data = await (await fetch(`${API_HOST}/admin/teacher/all/page/${page}`)).json();
        
        setData(data.teachers);
        setTotalPages(data._meta.pages);
    };

    getData();
}, [page]);

Then a table is rendered with a link to edit the teacher by allowing the user to navigate to the EditTeacher page,

Editing a Teacher

Once a teacher is associated with a user, this user can no longer be removed from the teacher. Therefore, the EditTeacher page only allows editing of the first, middle, and last name of the teacher.

useEffect(() => {
    // get the teacher
    const getTeacher = async () => {
        const teacher = await (await fetch(`${API_HOST}/admin/teacher/${id}`)).json();

        setFirstName(teacher.firstName);
        setMiddleName(teacher.middleName);
        setLastName(teacher.lastName);
        setUser({
            username: teacher.username,
            id: teacher.userId
        })
    };

    getTeacher();
}, [id]);

The response for GET /admin/teacher/:teacherId will also include the username, and user ID for the attached user.‌

Submitting the updated data is as easy as calling the PUT /admin/teacher request.

const response = await (await fetch(`${API_HOST}/admin/teacher`, {
    method: 'PUT',
    headers: {
        'Content-Type': 'application/json'
    },
    body: JSON.stringify({
        firstName,
        lastName,
        middleName,
        id,
        userId: user.id
    })
})).json();

Adding a Teacher to a Classroom

Now that I have the concept of both classroom, and teachers implemented, I now need to logically tie these two together. This is where the teacher_classroom table comes to use. So now, the task is deciding how to add an entry to this table to link the classroom, and teacher together.

AddClassroom, and EditClassroom are pages which should have a new field to allow assigning a teacher to a classroom. The POST, and PUT request should then include the teacherId when adding, or editing the classroom.

In the backend, changes are made to include the logic to either create, or update the teacher_classroom entry:

 app.post('/admin/classroom', async (req, res) => {
     const name = req.body.name;
     const slug = req.body.slug;
     const teacherId = parseInt(req.body.teacherId);

     if (!Number.isFinite(teacherId))
        return res.status(400).json({status: 'bad_request', message: 'Must provide a number for the teacher ID'});

     if (await Classroom.findBySlugIgnoreNotFound(slug))
        return res.status(400).json({message: 'Slug already exists'});

    const newClassroom = await new Classroom({
        name,
        slug
    }).insert();

    await new TeacherClassroom({
        teacherId,
        classroomId: newClassroom.id
    }).insert();

    return res.status(200).json({id: newClassroom.id});
 });

 app.put('/admin/classroom', async (req, res) => {
     const id = parseInt(req.body.id);

     if (!Number.isFinite(id))
        return res.status(400).json({message: 'Invalid classroom ID'});

    const name = req.body.name;
    const slug = req.body.slug;
    const teacherId = parseInt(req.body.teacherId);

    if (!Number.isFinite(teacherId))
        return res.status(400).json({status: 'bad_request', message: 'Must provide a number for the teacher ID'});

    let classroom = await Classroom.findBySlugIgnoreNotFound(slug);
    if (classroom && classroom.id !== id)
        return res.status(400).json({message: 'Invalid slug. Another classroom has already taken this'});
    
    // Slug has changed
    if (!classroom)
        classroom = await new Classroom().load(id);

    classroom.name = name;
    classroom.slug = slug;
    classroom.update();

    const currentTeacher = await TeacherClassroom.findTeacherByClassroomId(classroom.id);
    
    if (!currentTeacher)
        await new TeacherClassroom({
            classroomId: classroom.id,
            teacherId: teacherId
        }).insert();
    else {
        const teacherClassroom = await TeacherClassroom.findByTeacherAndClassroomId(currentTeacher.id, classroom.id);
        teacherClassroom.teacherId = teacherId;
        teacherClassroom.update();
    }

    return res.status(200).json({
        id: classroom.id,
        slug: classroom.slug,
        name: classroom.name
    });
 });

 app.delete('/admin/classroom/:classroomId', async (req, res) => {
     const id = parseInt(req.params.classroomId);

     if (!Number.isFinite(id))
        return res.status(400).json({message: 'Invalid classroom ID'});

    const classroom = await new Classroom().load(id);
    classroom.delete();

    // Remove the association between the classroom, and teacher.
    const teacher = await TeacherClassroom.findTeacherByClassroomId(classroom.id);
    const teacherClassroom = await TeacherClassroom.findByTeacherAndClassroomId(teacher.id, classroom.id);
    await teacherClassroom.delete();

    return res.status(200).json({
        id: classroom.id,
        dateDeleted: new Date(classroom.dateDeleted)
    });
 });

Two things that should be considered here:‌

To populate the drop down for the pages, I will just need to introduce an endpoint to get all teacher IDs.

app.get('/admin/teacher/list/all', async (req, res) => {
    const teachers = await Teacher.listAllTeachersNoLimit();

    return res.status(200).json({
        teachers: teachers.map(t => t.json())
    });
});

To get the available teachers from the front-end, it can just then be implemented like this:

useEffect(() => {
    if (id === 0)
        return;

    const fetchedClassroom = async () => {
        const teachers = await (await fetch(`${API_HOST}/admin/teacher/list/all`)).json();
        const data = await (await fetch(`${API_HOST}/admin/classroom/${id}`)).json();
        
        setName(data.name);
        setSlug(data.slug);
        setTeacherId(data.teacherId);

        if (teachers.teachers.length === 0)
            setTeachers([]);
        else
            setTeachers(teachers.teachers);
    };

    fetchedClassroom();
}, [id]);

Yay, lots of code… and now, a mess!