CSE 340: Web Backend Development

W04 Learning Activity: Assigning Categories to a Project

Overview

This activity will walk you through the process of assigning categories to a project. You will create a page that allows users to select categories for a project using checkboxes. Then, you will update the many-to-many table in the database to reflect the changes.

Activity Instructions

Complete the following steps to create the page to assign categories to a project.

Create the model functions

For this new functionality, you can re-use your existing functions to get all categories and to get categories assigned to a specific project.

You will create two new model functions for this functionality. First, you will create a function assignCategoryToProject that assigns a category to a project in the many-to-many relationship table.

Then, you will create a new model function updateCategoryAssignments that updates the categories assigned to a project. This function will receive two parameters: projectId and categoryIds (an array of desired category IDs for the project). The function will first remove any previous assignments, then for each categoryId in the new list, it will call the assignCategoryToProject function.

1. Open the src/models/categories.js model file.
2. Create a function assignCategoryToProject that takes two parameters: projectId and categoryId.
   • This function should execute a SQL query to insert a new record into the many-to-many relationship table that links projects and categories.
3. Create a function updateCategoryAssignments that takes two parameters: projectId and categoryIds (an array of category IDs that should be assigned to the project).
• This function should first execute a SQL query to delete all existing category assignments for the specified project from the many-to-many relationship table.
• Then, for each categoryId in the categoryIds array, call the assignCategoryToProject function to create the new assignments.
4. Add your updateCategoryAssignments function to the list of exports at the bottom of the file. (You do not need to add the assignCategoryToProject function to the exports because it is not used outside the model file.)

const assignCategoryToProject = async(categoryId, projectId) => {
    const query = `
        INSERT INTO project_category (category_id, project_id)
        VALUES ($1, $2);
    `;

    await db.query(query, [categoryId, projectId]);
}

const updateCategoryAssignments = async(projectId, categoryIds) => {
    // First, remove existing category assignments for the project
    const deleteQuery = `
        DELETE FROM project_category
        WHERE project_id = $1;
    `;
    await db.query(deleteQuery, [projectId]);

    // Next, add the new category assignments
    for (const categoryId of categoryIds) {
        await assignCategoryToProject(categoryId, projectId);
    }
}

Create the controller functions

Create controller functions to handle displaying the assign categories form and processing the form submission.

The request URL to display the form (GET) and process the form (POST) will be /assign-categories/:projectId where the projectId is a placeholder for the actual project ID.

1. Open the src/controllers/categories.js controller file.
2. Create a new function showAssignCategoriesForm.
   • Get the projectId from the request parameters.
   • This function should retrieve the project details using the existing getProjectDetails model function. (You will likely need to add this to your list of imports from the project model file).
   • It should also retrieve all categories using the existing getAllCategories model function.
   • Additionally, it should retrieve the categories currently assigned to the project using the existing getCategoriesByServiceProjectId model function. (You will likely need to add this to your list of imports from the categories model file.)
   • It should set the title variable to be, "Assign Categories to Project"
   • Finally, it should render a view assign-categories (to be created in the next step) and pass the project details, all categories, and the assigned categories to the view.
   • Create a new function processAssignCategoriesForm.
     • Get the projectId from the request parameters.
     • Get the selected category IDs from the request body. (Assume the form field name is categories and it will be an array of selected category IDs.)
     • This function should call the updateCategoryAssignments model function you created in the previous step, passing in the projectId and the array of selected category IDs. (You will likely need to add this to your list of imports from the categories model file.)
     • Set a success flash message.
     • Redirect the user back to the project details page /project/{projectId}.
   • Add these two controller functions to your list of exports in the controller file.

const showAssignCategoriesForm = async (req, res) => {
    const projectId = req.params.projectId;

    const projectDetails = await getProjectDetails(projectId);
    const categories = await getAllCategories();
    const assignedCategories = await getCategoriesByServiceProjectId(projectId);

    const title = 'Assign Categories to Project';

    res.render('assign-categories', { title, projectId, projectDetails, categories, assignedCategories });
};

const processAssignCategoriesForm = async (req, res) => {
    const projectId = req.params.projectId;
    const selectedCategoryIds = req.body.categoryIds || [];
    
    // Ensure selectedCategoryIds is an array
    const categoryIdsArray = Array.isArray(selectedCategoryIds) ? selectedCategoryIds : [selectedCategoryIds];
    await updateCategoryAssignments(projectId, categoryIdsArray);
    req.flash('success', 'Categories updated successfully.');
    res.redirect(`/project/${projectId}`);
};

Add routes

Add routes to your application for displaying the assign categories form and processing the form submission.

1. Open your main routes file src/controllers/routes.js .
2. Import the controller functions showAssignCategoriesForm and processAssignCategoriesForm from the categories controller file.
3. Add a GET route for /project/:projectId/assign-categories that calls the showAssignCategoriesForm controller function.
4. Add a POST route for /project/:projectId/assign-categories that calls the processAssignCategoriesForm controller function.

// Routes to handle the assign categories to project form
router.get('/assign-categories/:projectId', showAssignCategoriesForm);
router.post('/assign-categories/:projectId', processAssignCategoriesForm);

Create the view

Create a view template for the assign categories form. This form should display all categories with checkboxes. The checkboxes for the categories currently assigned to the project should be checked when the user visits the form.

1. Create a new EJS file src/views/assign-categories.ejs.
2. Create a form that submits to the POST route /assign-categories/:projectId.
3. Display the project title at the top of the form.
4. Loop through all categories and create a checkbox for each category.
   • The checkbox should be checked if the category is in the list of assigned categories for the project.
   • The checkbox name should be categoryIds so that it submits an array of selected category IDs.
5. Add a submit button to the form.
6. Add a link on the project page to navigate to the assign categories form. For example: <a href="/assign-categories/<%= project.project_id %>">Edit Categories</a>

<%- include('partials/header') %>
<main>
    <h1><%= projectDetails.title %></h1>

    <h2>Assign Categories</h2>
 <form action="/assign-categories/<%= projectId %>" method="POST">

    <div class="form-group">
      <% categories.forEach(category => { %>
        <div>
          <input
            type="checkbox"
            id="category-<%= category.category_id %>"
            name="categoryIds"
            value="<%= category.category_id %>"
            <%= assignedCategories.some(ac => ac.category_id === category.category_id) ? 'checked' : '' %>
          />
          <label for="category-<%= category.category_id %>"><%= category.name %></label>
        </div>
      <% }); %>
    </div>

    <button type="submit">Update Categories</button>

</main>
<%- include('partials/footer') %>

Test and deploy

Test the new functionality thoroughly to ensure that categories can be assigned and unassigned correctly. Once you are satisfied with the functionality, deploy your changes to your hosting platform.

Next Step

You have now completed all the learning activities for this week. Return to Canvas to submit a quiz.

Other Links:

• Return to: Week Overview | Course Home