# 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](https://react-select.com/home) 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](https://sequelize.org/docs/v6/core-concepts/assocs/#foobelongstomanybar--through-baz-) 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](https://react-select.com/home). 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](https://react-select.com/home#getting-started).

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

{% code title="NewSightingForm.js" %}

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

{% endcode %}

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](https://react-select.com/props#api) (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`.

{% code title="NewSightingForm.js" %}

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

{% endcode %}

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

{% hint style="info" %}
**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.

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

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

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

[React Select's styling documentation](https://react-select.com/styles#provided-styles-and-state) provides a quick overview.
{% endhint %}

#### 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](https://stackoverflow.com/a/25028339) 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](https://sequelize.org/docs/v6/core-concepts/assocs/#fetching-associations---eager-loading-vs-lazy-loading) and [`findByPk` API reference](https://sequelize.org/api/v6/class/src/model.js~model#static-method-findByPk) 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](https://react-select.com/home#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](https://github.com/rocketacademy/bigfoot-frontend-3.2/tree/solution-m-m-base) and the [backend](https://github.com/rocketacademy/bigfoot-sql-backend-bootcamp/tree/solution-sql-m-m-base) for this exercise, and here is a [reference deployment](https://bigfoot-solution-deployment.netlify.app/). You can do better!

### Deployment

You can also attempt to deploy your application, please follow [these instructions](https://bc.rocketacademy.co/3-backend/3.5-application-deployment).