Category Subcategory In NodeJS (Simple Example)

Welcome to a tutorial on how to deal with category and subcategory in NodeJS. So you are working on a project that involves categories in NodeJS. But the question is, how do we deal with this exactly? Well, let us walk through a simple example – Read on!

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

NODEJS CATEGORIES

All right, let us now get into the example of working with categories and subcategories in NodeJS. Take note, we will stick with SQLite here to keep things simple.

 

QUICK SETUP

Run npm i better-sqlite3 to install the required modules.

 

PART 1) THE DATABASE

1A) CATEGORY TABLE & DUMMY DATA

1a-database.sql
CREATE TABLE category (
  category_id INTEGER,
  parent_id INTEGER DEFAULT 0,
  category_name TEXT NOT NULL,
  PRIMARY KEY("category_id" AUTOINCREMENT)
);
CREATE INDEX parent_id ON category (parent_id);
 
INSERT INTO `category` (`category_id`, `parent_id`, `category_name`) VALUES
(1, 0, 'Electronics'),
(2, 1, 'Computers'),
(3, 1, 'Cameras'),
(4, 2, 'Desktop'),
(5, 2, 'Laptop');

That’s right, we pretty much only need a parent_id field to drive subcategories.

  • Categories with parent_id=0 are “root categories”.
  • Categories with parent_id>0 are “child categories”.

As for the dummy categories:

 

 

1B) CREATE DATABASE FILE

1b-database.js
require("better-sqlite3")("category.db").exec(
  require("fs").readFileSync("1a-database.sql", "utf8")
);
console.log("Database created.");

Next, let us create the database file itself. Remember to run npm install better-sqlite3 before this script.

 

PART 2) DRAWING THE CATEGORIES

2A) GET ALL CATEGORIES

2-draw.js
// (A) GET ALL CATEGORIES
const db = require("better-sqlite3")("category.db");
function getAll (id) {
  let rows = db.prepare("SELECT * FROM `category` WHERE `parent_id` = ?").all(id);
  if (rows.length==0) { return null; }
  let data = {};
  rows.forEach(row => {
    data[row["category_id"]] = {
      n : row["category_name"],
      c : getAll(row["category_id"])
    };
  });
  return data;
}

To draw the list of categories, you will first need a recursive function to get all the children. For those who are lost:

  • We will call getAll(0) on the first run to get the “root categories”.
  • In this example, the first run will result in data = { 1 : { n:ELECTRONICS, c:getAll(CHILD-ID) } }.
  • For the second round, data = { 2: { n:COMPUTERS, c:RECURSIVE }, 3: { n:CAMERAS, c:RECURSIVE } }.

Yep, this will “drill down” automatically until there are no more children.

 

 

2B) DRAW CATEGORIES

2-draw.js
// (B) DRAW CATEGORIES
function draw (data, level) {
  for (let [id, cat] of Object.entries(data)) {
    let row = ""; 
    if (level > 0) { for (i=0; i<level; i++) { row += " "; }}
    row += `(${id}) ${cat["n"]}`;
    console.log(row);
    if (cat["c"] != null) { draw(cat["c"], level+1); }
  }
}
draw(getAll(0), 0);

To draw the list… It’s the cursed recursion again. Loop through the above list of categories and draw accordingly.

P.S. I got lazy here and this just draws in plain text. Go ahead and do it in HTML or whatever “nice interface” you are working with.

 

PART 3) INSERT UPDATE DELETE

That’s about it for the “basic categories and subcategories”. But if you have to deal with the admin features, here are a few more examples.

 

 

3A) ADD & UPDATE CATEGORY

3-insert-update-delete.js
// (A) CONNECT TO DATABASE
const db = require("better-sqlite3")("category.db");
 
// (B) INSERT CATEGORY
let stmt = db.prepare("INSERT INTO `category` (`parent_id`, `category_name`) VALUES (?, ?)");
stmt.run(2, "Smartphones");
stmt.run(2, "Tablets");
 
// (C) UPDATE CATEGORY
stmt = db.prepare("UPDATE `category` SET `parent_id`=?, `category_name`=? WHERE `category_id`=?");
stmt.run(0, "Camerazzzz", 3);

Adding and updating categories is easy enough… But there’s a caveat, see below.

 

3B) THE PAIN OF CHANGING PARENT ID

3-insert-update-delete.js
// (D) GET CHILD CATEGORIES
function getChildren (id) {
  let rows = db.prepare("SELECT `category_id` FROM `category` WHERE `parent_id`=?").pluck().all(id);
  if (rows.length==0) { return null; }
  rows.forEach(i => {
    let children = getChildren(i);
    if (children != null) { rows = rows.concat(children); }
  });
  return rows;
}

// (E) GET VALID "SWITCHABLE PARENT ID"
function getSwitchable (id) {
  // (E1) CANNOT SWITCH TO CHILDREN & SELF
  let illegal = getChildren(id);
  if (illegal == null) { illegal = [id]; }
  else { illegal.push(id); }
 
  // (E2) GET "LEGAL CATEGORIES"
  let rows = db.prepare(
    "SELECT `category_id` FROM `category` WHERE `parent_id` NOT IN ("+illegal.join(",")+");"
  ).pluck().all();
  rows.push(0);
  return rows;
}

The painful part about updating categories is changing the parent. Consider the above example:

  • If we change “computers” to a “root category” – OK, there’s nothing wrong.
  • If we change “computers” to a child of “camera” – Weird, but the system will work as usual.
  • When we change “computers” to a child of “laptop” – Bad, laptop and desktop become orphaned categories.

So there must be rules when changing the parent ID.

  • A category can only “move up” or “move beside”.
  • A category must never be moved to a child within the same branch.
  • Neither can a category be the parent of itself.

 

 

3C) DELETE CATEGORY

3-insert-update-delete.js
// (F) DELETE CATEGORY
function del (id) {
  // (F1) REVERT CHILDREN TO "ROOT"
  let stmt, children = getChildren(id);
  if (children != null) {
    stmt = db.prepare("UPDATE `category` SET `parent_id`=0 WHERE `category_id` IN ("+children.join(",")+")");
    stmt.run();
  }

  // (F2) DELETE CATEGORY
  stmt = db.prepare("DELETE FROM `category` WHERE `category_id`=?");
  stmt.run(id);
}
del(2);

Finally, it’s the same for delete. What do we do with the child categories? In this example, we will revert all the children to the “root category”. But in your own project, you decide if you want to cascade delete.

 

EXTRAS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

EXTRA) SINGLE PARENT ONLY

The parent_id design will only allow categories to have one parent. If you want to allow “multiple parents”:

  • Create another category_to_category table with two fields – category_id and parent_id.
  • But beware, this also becomes very complex. You will have to do checks for stuff like “category A is the child of category B” and “category B is the child of category A”.

 

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *