3.E.4: Bigfoot SQL M-M

Learning Objectives

1. Understand how to set up M-M relationships in a SQL database using models and migrations with foreign keys and junction tables

2. Understand how to query M-M relationship data with Sequelize

3. Understand how to input M-M relationship data in an app

Introduction

We will add some weather categories to our Bigfoot sightings to make it easier for researchers to categorise sighting data. These weather categories could include values like 'Sunny', 'Raining' or even 'Snowing'. There will be an M-M relationship between categories and sightings.

Setup

If you haven't already, complete Exercise Setup for the prior Bigfoot SQL exercise. This exercise will use the same frontend and backend repos and the same setup. Remember to follow the MVC setup, create a new Controller and Router to support the Category Model.

Base: Add Categories to Sightings

We will follow a similar workflow to Bigfoot SQL 1-M in adding a new model, routes and frontend elements to support the categories feature.

Create Category model, associate with Sighting

Create new model and add associations

Create a new model Category to store sighting categories with npx sequelize model:generate, which will also create a corresponding migration. Reminder that our model names are Title Case with first letter capitalised. Category should have a name attribute of type string.

Update Category and Sighting models to include a belongsToMany association from each to the other, using the through option to specify junction table name sighting_categories.

Create migration for junction table

Create a new migration file for the sighting_categories junction table with npx sequelize migration:generate. Name the migration file with the same convention as the previous migration files, e.g. create-sightingcategories.

Update the migration for sighting_categories to create the junction table with id, sighting_id, category_id, created_at and updated_at attributes. Reference past migration files for sightings and comments tables for format of boilerplate attributes like id, created_at and updated_at and foreign key attributes like sighting_id and category_id. Remember to include the reverse instructions to drop the table in the down function, and verify our table name strings are accurate.

Run migrations

Run migrations with npx sequelize db:migrate and verify in our SQL client that our tables are what we expect. This would be a good time to commit changes to keep our commits small and with the app in working condition. Remember to include a short and descriptive commit message.

Write backend routes for categories

Clarify app requirements before creating backend routes

Before we start writing routes, let's understand app requirements and decide on inputs and outputs for our categories APIs.

We will need to add and remove categories when creating or editing sightings. We will use the React Select library in our frontend for users to select categories.

The above requirements mean we need 2 new routes: one to retrieve all categories and another to create a new category. We will also need to update our sighting creation route and sighting edit route (if any) to associate specified categories with sightings.

Let's start by creating the new routes to retrieve and create categories.

Create routes to retrieve and create categories

You will also need to create an additional controller, the categoriesController.js that should extend the base controller and be populated with the methods that will power facilitate category alterations to our database. In this case, you will need a method to create a new category instance, remember, as you are extending the base controller you have access to its method.

Create a new router file named categoriesRouter.js, model the sightingsRouter.js, create two requests, one a GET route to /categories to retrieve categories and a POST route to /categories to create a new category, the post route should read the category name from req.body.name. Ensure you bind these requests to the correct controller methods that you have just defined.

Besure to import the new categoriesRouter and categoriesController into the index.js and set up the middleware required to route our http requests, model how the sightingRouter and sightingController are implemented within the index.js.

Update sighting creation and edit routes (if any) to associate relevant categories with the relevant sighting

We want to remove associations with any no-longer-associated categories and add associations to all newly-associated categories. Luckily, Sequelize belongsToMany relationship methods provide us a convenient fooInstance.setBars() relationship method to do exactly this.

Unfortunately we are not yet sure exactly in what format our frontend will send category IDs to our backend, so let's come back to this task later.

Test routes with Thunder Client

Test that our GET and POST routes to /categories work with Thunder Client before moving on.

Update frontend to select categories on sighting creation

Update our new sighting form to add a categories field powered by React Select. React Select will provide a controlled form input whose value we control with a state variable. On form submit we will send the value of the state variable to our backend just like all other form inputs.

Add categories to database using POST requests with Thunder Client

To make our form more realistic, add at least 3 categories to our database via a new seeder file. We can generate the seeder file with npx sequelize seed:generate --name categories and populate the seeder file with content following the same format as our sightings seeder file. These categories can be rain, mountain, woods or any other categories you deem relevant.

Enable users to select categories with React Select

Install react-select as per React Select's Getting Started docs.

Include Select as a form field in our new sighting form. Rocket's reference implementation looks like the following.

NewSightingForm.js

<Select
  isMulti
  options={categoryOptions}
  value={selectedCategories}
  onChange={handleSelectChange}
/>

1. isMulti tells Select to accept multiple inputs

2. options are the category options in our select field that we retrieve with useEffect. Remember to pass [] as 2nd param to useEffect so the effect only runs on component mount!

3. value is the local state we use to control this form field. selectedCategories is local state we created with useState.

4. onChange is the callback method we use to update local state when the value of the select field changes. Unlike onChange for regular HTML input fields, onChange for React Select passes an array of selected values in { value, label } format as the 1st parameter to the callback function. This means we will need to create a custom handleSelectChange function separate from any handleChange functions we created for the other input fields. React Select onChange API documentation here (search for "onChange").

Rocket also included the following useEffect hook and logic to generate category options that may be helpful for you. allCategories is local state we created with useState.

NewSightingForm.js

useEffect(() => {
  axios.get(`${BACKEND_URL}/categories`).then((response) => {
    setAllCategories(response.data);
  });
  // Only run this effect on component mount
}, []);

const categoryOptions = allCategories.map((category) => ({
  // value is what we store
  value: category.id,
  // label is what we display
  label: category.name,
}));

Implement the above first, run our backend and frontend servers locally and verify we can receive all category options in our select field.

Control Select field colours

If you find that the Select field's colours are off, e.g. white text on white background, you can customise the Select field's styles relatively easily.

Rocket created the following Select style object in our sighting creation form.

// Make text black in Select field
const selectFieldStyles = {
  option: (provided) => ({
    ...provided,
    color: "black",
  }),
};

We then applied that style to our Select element like below.

<Select
  isMulti
  styles={selectFieldStyles}
  options={categoryOptions}
  value={selectedCategories}
  onChange={handleSelectChange}
/>

React Select's styling documentation provides a quick overview.

Update frontend and backend submit logic to create new sightings with category associations

Update our handleSubmit function to include selected category IDs when submitting new sighting data to our backend.

We now know our backend POST route to /categories will receive categories as an array of category IDs and can complete that route. In the route middleware function, retrieve relevant categories (you may find this Stack Overflow answer helpful) and associate them with the new sighting with the Sequelize belongsToMany fooInstance.setBars() (i.e. newSighting.setCategories()) relationship method.

Verify we can associate the new sighting with categories on submit. Great work!

Display categories next to sightings

Last but not least, let's display any associated categories next to sightings on the home page and the sighting-specific page.

Update routes to retrieve sightings to also retrieve category data

Update our backend routes that retrieve sightings (/sightings and /sightings/:sightingId) to also retrieve associated categories. We can do this with eager loading by specifying include: Category as query option. Review Sequelize's introduction to eager loading and findByPk API reference for examples and docs.

Verify with Thunder Client that these routes return category data together with sighting data.

Display category data next to sightings on homepage and sighting-specific page

Almost there! Now that our sighting retrieval routes respond with category data, update our UI logic to render associated categories next to each sighting on the homepage and sighting-specific pages. Congratulations!

Comfortable: Create New Categories

Use React Select Creatable features to allow users to create new categories while selecting categories. This may involve making an AJAX POST request to /categories in our Select field's onChange handler function to create a new category in the database, such that when we submit the category from our frontend, our backend can associate that category with the relevant sighting.

More Comfortable: Add Category Intensity

Add a non-foreign-key junction table column intensity to describe the intensity of each weather category associated to the sighting. Some values could include: 'heavy', 'light' and 'sparse', these values that could be used measure the intensity of the weather. This will require explicitly defining the SightingCategory model with intensity as an attribute, and creating a new migration to add the intensity column to the sighting_categories table.

Luckily, eager loading on retrieve sighting routes will already return any values in junction tables. We will need to update any forms where we select categories and components where we display categories to enable intensity selection and display.

React Select will not allow us to input metadata for each selected element. However, we can use JSX logic to render a numeric input field to capture intensity for each selected category in our form.

Submission

Submit pull requests to the main branches of Rocket's Bigfoot Frontend and Bigfoot SQL Backend repos respectively, and share your PR links in your sections Slack channel.

Reference Solution

Here is reference code for the frontend and the backend for this exercise, and here is a reference deployment. You can do better!

Deployment

You can also attempt to deploy your application, please follow these instructions.