-5

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    |                |
+-----------------+---------------+------+-----+---------+----------------+
  • Welcome to Software Engineering. We only support good, on-topic questions. Many sites have different rules. Feel free to take your issue to an appropriate site if one exists. Search existing answers first. Edit your question to fit the sites needs. Please don't cross post by failing to delete your question here. – candied_orange Oct 20 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.