Bootcamp
Search…
3.4.6: Forms for Many-to-Many Relationships

Introduction

One added complexity with many-to-many relationships is that form submissions need to update the join table that facilitates the many-to-many relationship. In this example we'll construct a form that associates a recipe with multiple categories by creating a set of rows in recipe_categories.
Unlike with the one-to-many example it's unwieldy to use a single text input for multiple categories. We could type multiple category IDs into the input (e.g. "5, 2, 3, 6, 9"), but this is prone to human error, and the categories would have to be processed into an array server-side.
1
<form action="/recipe/1/categories" method="POST">
2
<input type="text" name="category_ids" />
3
<input type="submit" value="Submit" />
4
</form>
Copied!
Similar to one-to-many forms, we also want the user to choose from named options without worrying about record IDs.

Input Relationship Data Using Checkboxes

One user-friendly way to manipulate many-to-many relationships is checkboxes. A checkbox is a UI element that represents zero or more selections. On the backend, we'll create a special route to associate categories with a given recipe.

GET Route for Recipe-Category Association Form

1
app.get('/recipes/:id/categories/add', (request, response) => {
2
pool.query('select * from categories', (error, result) => {
3
const data = {
4
categories: result.rows,
5
};
6
response.render('categories', data);
7
});
8
});
Copied!

EJS Template for Recipe-Category Association Form (categories.ejs)

1
<form action="/recipe/<%= recipieId %>/categories" method="POST">
2
<% categories.forEach((category) => { %>
3
<label><%= category.name %></label>
4
<input type="checkbox" name="category_ids" value="<%= category.id %>" />
5
<% }); %>
6
<input type="text" name="label" />
7
<input type="submit" value="Submit" />
8
</form>
Copied!
Notice that this is going to render a checkbox for each existing category. Every checkbox intentionally has the same name attribute to group the checkboxes together on form submission.
In our example of 3 categories, if all 3 categories are checked, the request will look like the following. Notice that the keys of the data being sent are the same each other, and the same as the name property of the checkbox form fields.

Handle Checkbox Data in Backend

If multiple checkboxes are checked in the form, Express.js groups form inputs with the same key into an array. If only 1 checkbox is checked, category_ids will contain a single ID and not an array. If 0 checkboxes are checked, request.body will not contain a category_ids key.
1
app.post('/recipie/:id/categories', (request, response) => {
2
console.log(request.body.category_ids); // will be an array of numbers
3
response.send('works');
4
});
Copied!
Now we will loop over the array of selected categories and insert the relevant entries to our join table recipe_categories in the DB.
1
app.post('/recipe/:id/categories', (request, response) => {
2
console.log(request.body.category_ids);
3
​
4
// get the recipe id url param
5
const recipeId = request.params.id;
6
​
7
const queryString =
8
'INSERT INTO recipe_categories (recipe_id, category_id) VALUES ($1, $2)';
9
​
10
// for each category we have in the request, make an insert query
11
request.body.category_ids.forEach((categoryId, index) => {
12
// construct the set of values we are inserting
13
const values = [recipeId, categoryId];
14
​
15
pool.query(queryString, values, (error, result) => {
16
// query is done
17
console.log(result);
18
});
19
});
20
​
21
response.send('done');
22
});
Copied!
Note that there is SQL syntax to insert multiple table entries with a single query, but pg does not support this elegantly. See more here.

Respond When All Insert Query Callbacks Are Done

In our previous example we may have sent a response to the client before all queries were completed. This is because pool.query is asynchronous, and response.send may run before all pool.query calls have completed. Instead, we may wish to send a response only when all insert queries are done.
To do this, we add logic to determine that category_ids.length number of queries have finished. We can replace the forEach block and the subsequent response.send call from the previous example with the following code.
1
let queryDoneCounter = 0;
2
​
3
// for each category we have in the request, make an insert query
4
request.body.category_ids.forEach((categoryId, index) => {
5
// construct the set of values we are inserting
6
const values = [recipeId, categoryId];
7
​
8
pool.query(queryString, values, (error, result) => {
9
// query is done
10
console.log(result);
11
​
12
// increment the counter, another query is done
13
queryDoneCounter += 1;
14
​
15
// check to see if all the queries are done
16
if (queryDoneCounter === request.body.category_ids.length) {
17
// TODO: check if any of the queries had errors.
18
​
19
// all the queries are done, send a response.
20
response.send('done!');
21
}
22
});
23
});
Copied!

Associate Categories On Recipe Creation

In addition to associating already-created recipes and categories, we may also want to associate recipes with categories at recipe creation. To do this, we'll add the above association logic to our recipe-creation logic.
In some sets of nested queries we need the result of an INSERT in order to proceed with the next query. Here on line 5 we are using RETURNING * at the end of our INSERT query so that we get back the primary key that was created.
1
app.post('/recipe', (request, response) => {
2
const recipeValues = [request.body.label];
3
​
4
// use "RETURNING *" to retrieve the newly-created row
5
const recipeInsertQuery = 'INSERT INTO recipes (label) VALUES ($1) RETURNING *';
6
​
7
// execute query to insert new recipe
8
pool.query(recipeInsertQuery, recipeValues, (recipeError, recipeResult) => {
9
if (recipeError) {
10
response.status(501).send('error!');
11
return;
12
}
13
​
14
// retrieving this data is possible because of the "RETURNING *" in our query
15
const recipeId = recipeResult.rows[0].id;
16
​
17
const categoryInsertQuery =
18
'INSERT INTO recipe_categories (recipe_id, category_id) VALUES ($1, $2)';
19
​
20
let queryDoneCounter = 0;
21
​
22
// for each category we have in the request, make an insert query
23
request.body.category_ids.forEach((categoryId, index) => {
24
// construct the set of values we are inserting
25
const categoryValues = [recipeId, categoryId];
26
​
27
pool.query(
28
categoryInsertQuery,
29
categoryValues,
30
(categoryError, categoryResult) => {
31
// query is done
32
console.log(categoryResult.rows);
33
​
34
queryDoneCounter += 1;
35
36
// all queries are done
37
if (queryDoneCounter === request.body.category_ids.length) {
38
response.send('done!');
39
}
40
}
41
);
42
});
43
});
44
});
Copied!