cklanac/challenge-08-one-to-many.md

## challenge-08-one-to-many.md

      
    Raw
  

              challenge-08-one-to-many.md
            
          
    Noteful Challenge - One-to-Many

In this challenge you will create a folders table, create a new router and endpoints for the folders, and update the existing notes endpoints to return folder related data.
Requirements


Create a folders table

Optionally, alter the default sequence so the folder IDs start at 100


Add a folder_id column to the notes table and define the relationship
Populate the folders table with sample data and update the notes data to include folders.
Create a folders router and all the standard (GET all, GET by id, POST, PUT and DELETE) endpoints

Get (GET) all folders
Get (GET) folder by id
Create (POST) a folder
Update (PUT) folder
Delete (DELETE) folder


Update the notes endpoints to return folder related data

Get all notes.

Plus, find notes in a folder


Get note by id
Create a new note
Update a note
Delete a note


Update the client-side code

Add folder table and relationships

To get started, let's create a folders table to the /db/noteful.sql file from the previous challenge. Optionally, you can alter the folders_id_seq so that it starts at a number other than default. Then populate it with sample data.
Add the following commands to your script file.
DROP TABLE IF EXISTS folders;

CREATE TABLE folders (
    id serial PRIMARY KEY,
    name text NOT NULL
);

Optional: ALTER SEQUENCE folders_id_seq RESTART WITH 100;

INSERT INTO folders (name) VALUES
  ('Archive'),
  ('Drafts'),
  ('Personal'),
  ('Work');
Using psql or your favorite GUI, verify the new table and the data.
SELECT * FROM folders;
Next, add a folder_id column to the notes table. Update the CREATE TABLE notes statement to include a folder_id along with the REFERENCES attribute.
CREATE TABLE notes (
  id serial PRIMARY KEY,
  title text NOT NULL,
  content text,
  created timestamp DEFAULT now(),
  folder_id int REFERENCES folders(id) ON DELETE SET NULL
);

Database Constraints - SQL gives us 3 options for dealing with related data when a record is deleted. Here's how the 3 options would work in our app.

ON DELETE SET NULL - If a folder is deleted, then set the folder_id to NULL for all related notes. This effectively removes them from a folder.
ON DELETE CASCADE - If a folder is deleted, then delete all the notes related ("inside") the folder.
ON DELETE RESTRICT - Prevent a folder from being deleted if it contains any notes.


For Noteful, we want the a note's folder_id to be set to null when the folder is deleted. So we'll use of ON DELETE SET NULL.
Update the INSERT INTO notes statement to include folder_id and add a value to the sample data. Here is an example, notice the folder id (100) towards the end of the statement.
INSERT INTO notes (title, content, folder_id) VALUES
  (
    '5 life lessons learned from cats',
    'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor...',
    100
  ),
Test your handy work.
-- get all notes with folders
SELECT * FROM notes
INNER JOIN folders ON notes.folder_id = folders.id;
-- get all notes, show folders if they exists otherwise null
SELECT * FROM notes
LEFT JOIN folders ON notes.folder_id = folders.id;
-- get all notes, show folders if they exists otherwise null
SELECT * FROM notes
LEFT JOIN folders ON notes.folder_id = folders.id;
WHERE notes.id = 1005;
Create router file and routes for Folders

Create ./routes/folders.js.
Add the require statement for express and knex then create an express.Router(). Remember to require and mount the router in server.js.
Back in the folders.js file create endpoints for the following actions:

Get All Folders (no search filter needed)
Get Folder by id
Update Folder The noteful app does not use this endpoint but we'll create it in order to round out our API
Create a Folder accepts an object with a name and inserts it in the DB. Returns the new item along the new id.
Delete Folder By Id accepts an ID and deletes the folder from the DB and then returns a 204 status.

To help get you started, here is an example of Get All folders endpoint
router.get('/folders', (req, res, next) => {
  knex.select('id', 'name')
    .from('folders')
    .then(results => {
      res.json(results);
    })
    .catch(err => next(err));
});
As you work, be sure to check your progress with Postman. Use Postman to select, create, update and delete folders and verify the results in the database. At any time, you can rerun the .sql script to drop the tables and repopulate them with sample data.
Update Notes Endpoints to include Folder data

Now that you can create, read, update and delete folders, you need to update the notes endpoints to include folders.
Get All Notes and Get Note By Id

In the notes.js find the GET /notes endpoint. Add a .leftJoin() to the query to include the related folder data in the results. Then test the results in Postman, you should see folder infomation in the JSON response.
knex.select('notes.id', 'title', 'content', 'folders.id as folderId', 'folders.name as folderName')
  .from('notes')
  .leftJoin('folders', 'notes.folder_id', 'folders.id')
  .modify(function (queryBuilder) {
    if (searchTerm) {
      queryBuilder.where('title', 'like', `%${searchTerm}%`);
    }
  })
  .orderBy('notes.id')
  .then(results => {
    res.json(results);
  })
  .catch(err => next(err));
Next, we want to add the ability to filter the results by a folderId. Add another .modify(...) method that conditionally adds a .where() clause to the query based on the folderId (shown below). Note, you'll also need to define folderId (not shown) similar to how searchTerm is defined.
knex.select('notes.id', 'title', 'content', 'folders.id as folderId', 'folders.name as folderName')
  .from('notes')
  .leftJoin('folders', 'notes.folder_id', 'folders.id')
  .modify(function (queryBuilder) {
    if (searchTerm) {
      queryBuilder.where('title', 'like', `%${searchTerm}%`);
    }
  })
  .modify(function (queryBuilder) {
    if (folderId) {
      queryBuilder.where('folder_id', folderId);
    }
  })
  .orderBy('notes.id')
  .then(results => {
    res.json(results);
  })
  .catch(err => next(err));
Test the endpoint using Postman.

Requesting http://localhost:8080/api/notes/?folderId=100 where folderId equals a valid id in your database should return a list of notes related with that folder.
Requesting http://localhost:8080/api/notes/?folderId=100&searchTerm=cats should return all the notes in the given folder that also contain the word cats.

Your Turn!
Update the GET notes by id endpoint with a .leftJoin() similar the one above. Then test it with Postman to verify the response contains the notes AND folder information.
Create a new Note and Update a Note

Creating and updating a note is a bit trickier. With the addition of Folders, you'll need to perform two queries to accomplish the entire task. The first query creates or updates the note, the second query selects the note and folders returns the new results. We chain the two queries together using promises so we are assured that the create or update has completed before making the select
In the notes.js find the POST /notes endpoint. Below is the skeleton solution along with comments, you'll need to implement the details. Some code "REMOVED FOR BREVITY".
router.post('/notes', (req, res, next) => {
  const { title, content, folderId } = req.body; // Add `folderId` to object destructure
  /*
  REMOVED FOR BREVITY
  */
  const newItem = {
    title: title,
    content: content,
    folder_id: folderId  // Add `folderId`
  };

  let noteId;

  // Insert new note, instead of returning all the fields, just return the new `id`
  knex.insert(newItem)
    .into('notes')
    .returning('id')
    .then(([id]) => {
      noteId = id;
      // Using the new id, select the new note and the folder
      return knex.select('notes.id', 'title', 'content', 'folder_id as folderId', 'folders.name as folderName')
        .from('notes')
        .leftJoin('folders', 'notes.folder_id', 'folders.id')
        .where('notes.id', noteId);
    })
    .then(([result]) => {
      res.location(`${req.originalUrl}/${result.id}`).status(201).json(result);
    })
    .catch(err => next(err));
});
Your turn!
Update the PUT /notes endpoint using the same techniques. When you're done, check all the endpoints using postman and cross-check against the DB.
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/folders')
    .then(response => {
      store.folders = response;
      noteful.render();
    });
In noteful.js, find the section titled "FOLDERS EVENT LISTENERS AND HANDLERS". There are 3 functions in the section: handleFolderClick(), handleNewFolderSubmit() and handleFolderDeleteClick(). Uncomment the app.METHOD calls and remove the console.log('... coming soon'); statements.
The handleFolderClick()

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

Listens for the new folder 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 handleFolderDeleteClick()

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.