Nested Json response from NodeJs and MySql

by Gitonga Tosh   Last Updated October 19, 2019 19:05 PM - source

I have 3 Mysql tables, a vendor table with a list of vendors, a categories table with a list of categories that these vendors have in there stores, the categories table is linked to to the vendors table with a secondary key(vendor_id), i also have a product table that has all the products in these categories and it is linked to the category table using a foreign key (category_id). How do i write a NodeJs/ express route that returns a json object that looks like this,

    {
    "id": 1,
    "vendor": "Food place",
    "category": "Chicken"[
                     {
                        "id": "1,            
                        "name": "Fried chicken",
                        "price": "100"
                     },
                     {
                        "id": "2,            
                        "name": "Soup Chicken",
                        "price": "150"
                     }
}

So the json object returns a vendor all the categories they have in there store, and inside the categories all the products that be long to these categories

The tables look similar to this Vendor table:

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255) | NO   |     | NULL    |                |
| category | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

Category table:

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20) | YES  |     | NULL    |                |
| vendor_id | int(11)     | NO   | MUL | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

Product table:

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(11)       | NO   | PRI | NULL    | auto_increment |
| price           | decimal(10,0) | NO   |     | NULL    |                |
| name            | varchar(255)  | YES  |     | NULL    |                |
| category_id     | int(11)       | YES  | MUL | NULL    |                |
+-----------------+---------------+------+-----+---------+----------------+


Related Questions


Dynamic API Data Validation

Updated March 05, 2019 22:05 PM

NodeJs code encryption

Updated March 28, 2019 09:05 AM

Cannot get one Row with MySQL and ReactJS

Updated July 14, 2018 16:05 PM

Numtel mysql guide meteor

Updated July 06, 2017 16:05 PM