Skip to content

Instantly share code, notes, and snippets.

@cklanac

cklanac/challenge-09-many-to-many.md

Last active June 26, 2024 18:32
Show Gist options
  • Save cklanac/3e1dadac4ee6ce007737e698eaa89369 to your computer and use it in GitHub Desktop.
Save cklanac/3e1dadac4ee6ce007737e698eaa89369 to your computer and use it in GitHub Desktop.
Download ZIP
Challenge 09: KnexJS with Many-to-Many
Raw
challenge-09-many-to-many.md

Noteful Challenge - Many-to-Many

In this challenge you will create a tags and notes_tags table which will setup a many-to-many relationship between notes and tags. You'll also create a new router to perform CRUD operations on the tags, and update the existing notes endpoints to work with tag related data.

Requirements

Add tags and notes_tags table and relationships

To get started, let's update the .sql script from the previous challenges. Create a tags table and define 2 fields in the schema.

  • An id as a primary key
  • A name text field that can not be null (Optionally, make it UNIQUE)

This process is similar to the previous challenge so we won't bore you with sample code.

Next, create a notes_tags junction table which, unsurprisingly, references the notes and the tags tables. We want the entries in this table to be deleted if either the related note or tag is deleted, so add ON DELETE CASCADE to field schema.

CREATE TABLE notes_tags (
  note_id INTEGER NOT NULL REFERENCES notes ON DELETE CASCADE,
  tag_id INTEGER NOT NULL REFERENCES tags ON DELETE CASCADE
);

To adequately test the schemas, you'll need to populate the tables with dummy data. Insert a few tags into the tags table and insert several rows into the notes_tags table which relate a note to one or more tags.

Check your setup by running queries against your database. Test the database using simple queries against individual tables and complex queries which JOIN the tables. Experiment with INNER JOINS compared to both LEFT OUTER JOIN and RIGHT OUTER JOIN. Here's one example to get you started.

SELECT title, tags.name, folders.name FROM notes
LEFT JOIN folders ON notes.folder_id = folders.id
LEFT JOIN notes_tags ON notes.id = notes_tags.note_id
LEFT JOIN tags ON notes_tags.tag_id = tags.id;

Create a Tags router file and routes

In ./routes directory, create a tags.js file. And add require statements for express and knex, and create an instance of express.router. Don't forget to wire up the new router in the server.js file.

Below is an example using the router.post('/tags',... endpoint. Your challenge, is to create the standard 5 CRUD endpoints ( GET all, GET by id, POST, PUT, DELETE ).

/* ========== POST/CREATE ITEM ========== */
router.post('/tags', (req, res, next) => {
  const { name } = req.body;

  /***** Never trust users. Validate input *****/
  if (!name) {
    const err = new Error('Missing `name` in request body');
    err.status = 400;
    return next(err);
  }

  const newItem = { name };

  knex.insert(newItem)
    .into('tags')
    .returning(['id', 'name'])
    .then((results) => {
      // Uses Array index solution to get first item in results array
      const result = results[0];
      res.location(`${req.originalUrl}/${result.id}`).status(201).json(result);
    })
    .catch(err => next(err));
});

Update Notes Endpoints to include tag data

Add Tags to the Get All Notes and Get Note By Id

Get All Notes

Similar to the previous challenge, you need to update the router.get('/notes',... endpoint include the related tag data in the results. In this round, you'll add 2 .leftJoin() clauses. One to join notes with note_tags and another to join notes_tags with tags

folders <=> notes <=> notes_tags <=> tags

Your challenge, add the .leftJoin() clauses to the .select query in the router.get('/notes',... endpoint.

If you test the localhost:8080/api/notes endpoint in Postman, you'll notice that notes are repeated for each associated tag. Obviously, this is not what you want. You need to transform the tabular (columns and rows) data into an object with an array of tags. This process is known as hydration.

Create a /utils/hydrateNotes.js file. In the file add the following hydrate function and remember to export the module so it can be imported and used elsewhere.

Don't worry. We'll walk thru the hydration process in detail in class.

function hydrateNotes(input) {
  const hydrated = [], lookup = {};
  for (let note of input) {
    if (!lookup[note.id]) {
      lookup[note.id] = note;
      lookup[note.id].tags = [];
      hydrated.push(lookup[note.id]);
    }

    if (note.tagId && note.tagName) {
      lookup[note.id].tags.push({
        id: note.tagId,
        name: note.tagName
      });
    }
    delete lookup[note.id].tagId;
    delete lookup[note.id].tagName;
  }
  return hydrated;
}

In the notes.js file, import the hydration module like so

const hydrateNotes = require('../utils/hydrateNotes');

And update the .then() in your Get all notes endpoint. Below is sample.

  .then(result => {
    if (result) {
      const hydrated = hydrateNotes(result);
      res.json(hydrated);
    } else {
      next();
    }
  })

Get All Notes (filter by tag id)

In the Get All Notes router (router.get('/notes',...), you need to add the ability to search for notes by tag id. We'll use a querystring to pass the tagId, similar to searching for notes by Folder. The request will look like this http:/localhost:8080/api/tags?tagId=123.

In your route handler:

  • Capture the tagId from the querystring (similar to folderId solution)
  • Add a .modify() condition along with a subquery to get the list of IDs.

Below is sample .modify() clause along with the subquery

    .modify(function (queryBuilder) {
      if (tagId) {
        queryBuilder.where('tag_id', tagId);
      }
    })

Get Notes By Id

Your Turn!

Implement the same process for the GET notes by id endpoint. It will require the .leftJoin()'s as well as the hydrate process. But it does not need the conditional .modify() clauses to filter by searchTerm, folderId or tagId.

Add Tags to the create a new Note and update a Note endpoints

Add Tags to the create endpoint

Adding tags to the create endpoint involves some additional hurdles. In order to insert into the notes_tags table you need the note Id, which means you need to insert the note first to get back an Id. And then insert the notes_tags. This involves chaining promises. Below is an outline of the process.

  Insert new note into notes table
    then
      Insert related tags into notes_tags table
    then
      Select the new note and leftJoin on folders and tags
    then
      if the result exists
        hydrate the results
        and Respond with a location header, a 201 status and a note object
      else
        trigger a 404

The incoming request to create a new note will have a tags property with an array of ids like the following sample:

{
      "title": "5 life lessons learned...",
      "content": "Lorem ipsum dolorums...",
      "folder_id": 101,
      "tags":[34, 56, 78]
  }

Below is the complete query.

let noteId;
// Insert new note into notes table
knex.insert(newItem).into('notes').returning('id')
  .then(([id]) => {
    // Insert related tags into notes_tags table
    noteId = id;
    const tagsInsert = tags.map(tagId => ({ note_id: noteId, tag_id: tagId }));
    return knex.insert(tagsInsert).into('notes_tags');
  })
  .then(() => {
    // Select the new note and leftJoin on folders and tags
    return knex.select('notes.id', 'title', 'content',
      'folders.id as folder_id', 'folders.name as folderName',
      'tags.id as tagId', 'tags.name as tagName')
      .from('notes')
      .leftJoin('folders', 'notes.folder_id', 'folders.id')
      .leftJoin('notes_tags', 'notes.id', 'notes_tags.note_id')
      .leftJoin('tags', 'tags.id', 'notes_tags.tag_id')
      .where('notes.id', noteId);
  })
  .then(result => {
    if (result) {
      // Hydrate the results
      const hydrated = hydrateNotes(result)[0];
      // Respond with a location header, a 201 status and a note object
      res.location(`${req.originalUrl}/${hydrated.id}`).status(201).json(hydrated);
    } else {
      next();
    }
  })
  .catch(err => next(err));

Let's review the "Insert related tags into notes_tags table" section.

The .map() converts an array of tagIds like [34, 56, 78] in to an array of objects with a note_id and a tag_id. And then inserts them into the notes_tags table

Try it out. Copy the following into a scratch file and run it. Experiment with the code to make sure you understand it before proceeding.

const noteId = 99;
const result = [34, 56, 78].map(tagId => ({ note_id: noteId, tag_id: tagId }));
console.log(`insert: ${result} into notes_tags`);

Add Tags to the notes update endpoint

The update process is similar to the create/insert endpoint above, with one additional hurdle. The notes_tags table already contains related tags. The simplest way to solve the problem is to first delete the existing notes_tags rows for the given noteId. And then reinsert them, thus ensuring the new relations are added, the removed relations are deleted and the modified relations are updated. Below is an outline of the process.

  Update note in notes table
    then
      Delete current related tags from notes_tags table
    then
      Insert related tags into notes_tags table
    then
      Select the new note and leftJoin on folders and tags
    then
      if the result exists
        hydrate the results
        and respond with a 200 status and a note object
      else
        trigger a 404

Your challenge is to modify the update notes endpoint (router.put('/notes/:id',...). Use the create/insert endpoint provided above as a guide.

Test the endpoints thoroughly using Postman. You should test all 15 endpoints:

  • Folders
    • Get (read) all
    • Get (read) by id
    • Post (create)
    • Put (update)
    • Delete
  • Tags
    • Get (read) all
    • Get (read) by id
    • Post (create)
    • Put (update)
    • Delete
  • Notes:
    • Get (read) all
    • Get (read) by id
    • Post (create)
    • Put (update)
    • Delete

Enable the client-side features

There are several features in the client-side code which have been disable. Let's enable the folder related features.

In index.js, uncomment the API search that calls /api/folders.

  api.search('/api/tags')
    .then(response => {
      store.folders = response;
      noteful.render();
    });

In noteful.js, find the section titled "TAGS EVENT LISTENERS AND HANDLERS". There are 3 functions in the section: handleTagClick(), handleNewTagSubmit() and handleTagDeleteClick(). Uncomment the app.METHOD calls and remove the console.log('... coming soon'); statements.

The handleTagClick()

Adds an event listener that responds to clicking a tag in the list. This function is very similar to handleNoteItemClick().

The handleNewTagSubmit()

Listens for the new tag submit. It captures the user input and pass it to the correct api.create method. When the request returns it chains an api.search request which will update the notes list.

The handleTagDeleteClick()

Listens a click to the delete ("X") button. If successful, it makes a second call to get a fresh list of folders and renders the app.

All 3 functions are invoked in bindEventListeners() which called on Document Ready.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment