· 8 years ago · Feb 20, 2018, 12:54 PM
1# Noteful Challenge - One-to-Many
2
3In 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.
4
5## Requirements
6
7* Create a `folders` table
8 * Optionally, alter the default sequence so the folder IDs start at 100
9* Add a `folder_id` column to the `notes` table and define the relationship
10* Populate the `folders` table with sample data and update the `notes` sample data to include folders.
11* Create a `folders` router and all the standard (GET x 2, POST, PUT and DELETE) endpoints
12 * Get (GET) All Folders
13 * Get (GET) Folder By Id
14 * Create (POST) a Folder
15 * Update (PUT) Folder
16 * Delete (DELETE) Folder
17* Update the `notes` endpoints to return folder related data
18 * Get All Notes.
19 * Plus, find notes in a folder
20 * Get Note By Id
21 * Create a new Note
22 * Update a Note
23 * ~~Delete a Note~~
24* Update the client-side code
25
26## Add `folder` table and relationships
27
28To get started, let's create a `folders` table to your `.sql` script 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.
29
30Add the following commands your to existing `.sql` script file.
31
32```sql
33DROP TABLE IF EXISTS folders;
34
35CREATE TABLE folders (
36 id serial PRIMARY KEY,
37 name text NOT NULL UNIQUE
38);
39```
40
41> Optional: `ALTER SEQUENCE folders_id_seq RESTART WITH 100;`
42
43```sql
44INSERT INTO folders (name) VALUES
45 ('Archive'),
46 ('Drafts'),
47 ('Personal'),
48 ('Work');
49```
50
51Verify the new table and the data by quering it in `psql` or your favorite GUI
52
53```sql
54SELECT * FROM folders;
55
56SELECT * FROM folders WHERE id = 103;
57```
58
59Next, 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.
60
61```sql
62CREATE TABLE notes (
63 id serial PRIMARY KEY,
64 title text NOT NULL,
65 content text,
66 created timestamp DEFAULT now(),
67 folder_id int REFERENCES folders ON DELETE SET NULL
68);
69```
70
71> Database Constraints - The requirements for our app state that when a user deletes a folder that any notes inside the folder should be moved to the "All" category. Hence the use of `ON DELETE SET NULL`. For our current application, changing constraints changes the behavior of our app as follows.
72> * `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.
73> * `ON DELETE CASCADE` - If a folder is deleted, then delete all the notes related ("inside") the folder.
74> * `ON DELETE RESTRICT` - Prevent a folder from being deleted if it contains any notes.
75
76Update the `INSERT INTO notes` statement to include `folder_id` and add a value to the sample data.
77
78```sql
79INSERT INTO notes (title, content, folder_id) VALUES
80 (
81 '5 life lessons learned from cats',
82 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.',
83 100
84 ),
85```
86
87Test your handy work.
88
89```sql
90-- get all notes with folders
91SELECT * FROM notes
92INNER JOIN folders ON notes.folder_id = folders.id;
93```
94
95```sql
96-- get all notes, show folders if they exists otherwise null
97SELECT * FROM notes
98LEFT JOIN folders ON notes.folder_id = folders.id;
99```
100
101```sql
102-- get all notes, show folders if they exists otherwise null
103SELECT * FROM notes
104LEFT JOIN folders ON notes.folder_id = folders.id;
105WHERE notes.id = 1005;
106```
107
108## Create router file and routes
109
110In `./routes` directory, create a `folders.router.js` file.
111
112Add the require statement for `express` and `knex` then create an `express.router`. Refer to `notes.router.js` if needed. Remember to require the router in the server.js file and mount it correctly.
113
114Add the following to the appropriate sections of your server.js file
115
116```js
117const foldersRouterV2 = require('./routes/folders.router');
118```
119
120and
121
122```js
123app.use('/v2', foldersRouterV2);
124```
125
126Back in the `folders.router.js` file create endpoints for each of the primary RESTful actions
127
128* Get All Folders (no `searchTerm` needed)
129* Get Folder By Id
130* Folder Update - The noteful app does not use this endpoint but we'll create it in order to round out our RESTful service
131* Create a Folder accepts an object with a `name` and inserts it in the DB. Returns the new item along the new id.
132* Delete Folder By Id accepts an ID and deletes the folder from the DB and then returns a 204 status.
133
134To help get you started, here is an example of Get All folders endpoint
135
136```js
137router.get('/folders', (req, res, next) => {
138 knex.select('id', 'name')
139 .from('folders')
140 .then(results => {
141 res.json(results);
142 })
143 .catch(next);
144});
145```
146
147As you work, be sure to check your progress with Postman. Create, update and delete folders and verify the changes in the database. You can rerun the `.sql` script to drop the tables and repopulate them with sample data.
148
149## Update Notes Endpoints to include Folder data
150
151### Get All Notes and Get Note By Id
152
153In the `router.get('/notes', ` endpoint, update the query with an `.leftJoin()` to include the related folder data in the results. Test the results in Postman.
154
155```js
156knex.select('notes.id', 'title', 'content', 'folder_id', 'folders.name as folder_name')
157 .from('notes')
158 .leftJoin('folders', 'notes.folder_id', 'folders.id')
159 .where(function () {
160 if (searchTerm) {
161 this.where('title', 'like', `%${searchTerm}%`);
162 }
163 })
164 .orderBy('notes.id')
165 .then(results => {
166 res.json(results);
167 })
168 .catch(err => {
169 console.error(err);
170 });
171});
172```
173
174Your Turn! Perform a similar update to the `router.get('/notes/:id', ` endpoint. Again, test the results in Postman.
175
176Returning to the GET all Notes endpoint above, let's add the ability to search or filter the results by a folderId. The `folderId` will be passed in the querystring like `localhost:8080/notes/?folderId=123`. Add the following `.where()` condition to the query. Notice, it only gets executed if the folderId is provided.
177
178```js
179 .where(function () {
180 if (req.query.folderId) {
181 this.where('folder_id', req.query.folderId);
182 }
183 })
184```
185
186Check your results.
187
188### Create a new Note and Update a Note
189
190Creating 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
191
192Below is the skeleton solution along with comments, you'll need to implement the details.
193
194In the `router.post('/notes',` endpoint:
195
196```js
197router.post('/notes', (req, res, next) => {
198 const { title, content, folder_id } = req.body; // Add `folder_id`
199 /*
200 REMOVED FOR BREVITY
201 */
202 const newItem = {
203 title: title,
204 content: content,
205 folder_id: folder_id // Add `folder_id`
206 };
207
208 let noteId;
209
210 // Insert new note, instead of returning all the fields, just return the new `id`
211 knex.insert(newItem)
212 .into('notes')
213 .returning('id')
214 .then(([id]) => {
215 noteId = id;
216 // Using the new id, select the new note and the folder info
217 return knex.select('notes.id', 'title', 'content', 'folder_id', 'folders.name as folder_name')
218 .from('notes')
219 .leftJoin('folders', 'notes.folder_id', 'folders.id')
220 .where('notes.id', noteId);
221 })
222 .then(([result]) => {
223 res.location(`${req.originalUrl}/${result.id}`).status(201).json(result);
224 })
225 .catch(err => {
226 console.error(err);
227 });
228});
229```
230
231Your turn. Update the `router.put('/notes/:id',` endpoint using the same techniques. When you're done, check all the endpoints using postman.
232
233> Notice, the `knex.select('notes.id', 'title', ` in the POST and PUT endpoints is similar to the GET endpoints. You'll work on making this DRY after we add the Tags in a later challenge.
234
235## Update the client-side code
236
237On `index.html` uncomment the folders section along with the folder select
238
239Towards the top of the file, uncomment the following section:
240
241```html
242 <header>
243 <h2>Folders</h2>
244 <form id="new-folder-form" class="js-new-folder-form">
245 <input type="text" class="js-new-folder-entry" placeholder="folder name">
246 <button type="submit">add</button>
247 </form>
248 </header>
249 <ul class="js-folders-list"></ul>
250```
251
252Lower on the page, uncomment the folder `<select>` tag.
253
254```html
255 <select name="folder" class="js-note-folder-entry"></select>
256```
257
258In `store.js` file add the following to the `store` to hold the folders list
259
260```js
261 folders: [],
262```
263
264On `index.js` add an API search which calls the `/v2/folders/` endpoint, saves the results in the store and calls the `render()` method.
265
266```js
267 api.search('/v2/folders')
268 .then(response => {
269 store.folders = response;
270 noteful.render();
271 });
272```
273
274Now, let's update the `render()` method to populate the folders section and select dropdown. In `noteful.js`, add the following to the `render()` function. This calls `generateFolderList()` which we'll create next.
275
276```js
277 const folderList = generateFolderList(store.folders, store.currentQuery);
278 $('.js-folders-list').html(folderList);
279```
280
281Create the `generateFolderList()` function which accepts the list of folders to generate. And the ID of the currently selected folder, if any.
282
283```js
284 function generateFolderList(list, currQuery) {
285 const showAllItem = `
286 <li data-id="" class="js-folder-item ${!currQuery.folderId ? 'active' : ''}">
287 <a href="#" class="name js-folder-link">All</a>
288 </li>`;
289
290 const listItems = list.map(item => `
291 <li data-id="${item.id}" class="js-folder-item ${currQuery.folderId === item.id ? 'active' : ''}">
292 <a href="#" class="name js-folder-link">${item.name}</a>
293 <button class="removeBtn js-folder-delete">X</button>
294 </li>`);
295
296 return [showAllItem, listItems].join('');
297 }
298```
299
300Now, when you load the client, the folders should populate in the folder nav section. Let's also populate the folder dropdown in the Note edit form.
301
302Back in the `render` method, add a call to `generateFolderSelect()` which you'll create shortly.
303
304```js
305const folderSelect = generateFolderSelect(store.folders);
306 $('.js-note-folder-entry').html(folderSelect);
307```
308
309And now, create the `generateFolderSelect()` method
310
311```js
312function generateFolderSelect(list) {
313 const notes = list.map(item => `<option value="${item.id}">${item.name}</option>`);
314 return '<option value="">Select Folder:</option>' + notes.join('');
315 }
316```
317
318Refresh the app and the folder select dropdown in the Note edit for should populate. To wrap up this section, we want to add the ability to select the current folder in the drop down. Add the following to the `render()` method.
319
320```js
321 //NOTE: Incoming folder id for API is `folder_id`, locally it is folderId
322 editForm.find('.js-note-folder-entry').val(store.currentNote.folder_id);
323```
324
325Let's add an event listener to respond to clicking an item in the list. This function is very similar to `handleNoteItemClick()`. Remember to add a reference to the `bindEventListeners()` to run the function on document ready.
326
327```js
328 function handleFolderClick() {
329 $('.js-folders-list').on('click', '.js-folder-link', event => {
330 event.preventDefault();
331
332 const folderId = getFolderIdFromElement(event.currentTarget);
333 store.currentQuery.folderId = folderId;
334 if (folderId !== store.currentNote.folder_id) {
335 store.currentNote = {};
336 }
337
338 api.search('/v2/notes', store.currentQuery)
339 .then(response => {
340 store.notes = response;
341 render();
342 });
343 });
344 }
345```
346
347Add a function to listen for the new folder submit. It must capture the user input and pass it to the correct `api.create` method. When the request returns then chain an `api.search` request which will update the notes list. Again, remember to add the function to the `bindEventListeners()` method.
348
349```js
350 function handleNewFolderSubmit() {
351 $('.js-new-folder-form').on('submit', event => {
352 event.preventDefault();
353
354 const newFolderName = $('.js-new-folder-entry').val();
355 api.create('/v2/folders', { name: newFolderName })
356 .then(() => {
357 $('.js-new-folder-entry').val();
358 return api.search('/v2/folders');
359 }).then(response => {
360 store.folders = response;
361 render();
362 }).catch(err => {
363 $('.js-error-message').text(err.responseJSON.message);
364 });
365 });
366 }
367```
368
369> Going Pro: As we mentioned in the previous challenges, this brute-force approach creates additional load on the server. Ideally, you would update the store to avoid the extra requests which you will tackle later with React and Redux.
370
371Lastly, you will create a function to listen for respond to folder delete event. Add the following and update the `bindEventListeners()`
372
373```js
374 function handleFolderDeleteClick() {
375 $('.js-folders-list').on('click', '.js-folder-delete', event => {
376 event.preventDefault();
377 console.log(6798);
378
379 const folderId = getFolderIdFromElement(event.currentTarget);
380
381 if (folderId === store.currentQuery.folderId) {
382 store.currentQuery.folderId = null;
383 }
384 if (folderId === store.currentNote.folder_id) {
385 store.currentNote = {};
386 }
387
388 api.remove(`/v2/folders/${folderId}`)
389 .then(() => {
390 return api.search('/v2/folders');
391 })
392 .then(response => {
393 store.folders = response;
394 render();
395 });
396 });
397 }
398```