Create API in node js to get data with multiple queries with my sql

Question

I am new to node js. I am trying to develop API for getting items list by its category list. For that i have create a function to fetch all available active featured tags from table ( featured_tags ). After that featured tag list fetch i want to get items list from ( items ) table which belongs to that particular tag. Can anyone help me how to so that in node js. I am using mysql database.

i have done below things to fetch categories from table.

route.js file

this.app.get('/list_menu',async(request,response) =>{
        var itemlist = ''; 
        const featuretags = helper.getFeatureTag().then(function(featuredtags){
                //console.log('test');
                itemlist = helper.getitems(featuredtags);

            });
        response.status(200).json({
                        status:200,
                        message: "success",
                        data:itemlist
                });

    });

function to get active featured tags in helper.js file

async getFeatureTag(){
    return this.db.query("select * from featured_tags where status = 1 order by id desc ");
    //const featuredtags = await this.db.query("select * from featured_tags where status = 1 order by id desc ");
}

Function which get items list of featured tags in helper.js file

async getitems(featuredtags){
    var itemdata = [];
    var featured_tagdataset = [];
    if(featuredtags.length > 0){
        for (var i = 0; i < featuredtags.length; i++) {
            var row = featuredtags[i];
            var featurtag = {};
            featurtag.id = row.id;
            featurtag.featured_tag = row.featured_tag;
            var itemresult = await this.db.query("SELECT * FROM `items` WHERE status = 1 and FIND_IN_SET('"+ row.id +"' ,featured_tags) > 0");
            if(itemresult.length > 0){
                for(var l=0; l < itemresult.length; l++){
                    var itemrow = itemresult[l];
                    var item = {};
                    item.id = itemrow.id;
                    item.category_id = row.id;
                    item.name = itemrow.item_name;
                    itemdata.push(JSON.stringify(item));
                }                   
            }
            featurtag.tag_items = itemdata;
            featured_tagdataset.push(featurtag);
        }
        //console.log(featured_tagdataset);
        return featured_tagdataset;         
    }else{
        return null;
    }
}

when i console featuredtag_dataset array in itemlist() in helper.js file it show me perfect response which i have to pass in API response. But in route.js it shows me blank in data parameter.

Can anyone help me for how to develop this type of APIs in node js.

Why are you executing two queries ? from the requirement stated above the task can be done in one query only. You can use Joins in mysql to join the combined result of the two queries. This way you can retrieve the result from one database interaction only. And to carry out the task asynchronously use await/async , this way you can prevent the code in getting stuck in callback hell scenario. — INDRESH KHANDELWAL
– INDRESH KHANDELWAL, Commented Dec 10, 2018 at 8:39

eagle · Accepted Answer · 2018-12-10 05:59:13Z

2

This is because helper.getitems(featuredtags) method is called successfully but send response doesn't wait until method returns a response as node js is asynchronous .

you need to write the code in such a way that it should work in series. I have created sample example you can try this.

this.app.get('/list_menu',async(request,response) =>{
        helper.getFeatureTag().then(function(featuredtags){
            helper.getitems(featuredtags).then(function(itemlist){
                response.status(200).json({
                        status:200,
                        message: "success",
                        data:itemlist
                });
            })

        }
});

answered Dec 10, 2018 at 5:59

eagle

3222 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NAVIN · Accepted Answer · 2018-12-10 06:36:38Z

0

You forget to use await in your roter.js on calling asynchronous function, just update your router to this

this.app.get('/list_menu',async(request,response) =>{
    const featuredtags = await helper.getFeatureTag(),
          itemlist = await helper.getitems(featuredtags);

    response.status(200).json({
        status:200,
        message: "success",
        data:itemlist
    });

});

answered Dec 10, 2018 at 6:36

NAVIN

3,3174 gold badges21 silver badges32 bronze badges

Comments

Golwin · Accepted Answer · 2018-12-10 06:49:49Z

0

you can either nested callback function or async await function or chained promises using then.

answered Dec 10, 2018 at 6:49

Golwin

15713 bronze badges

Collectives™ on Stack Overflow

Create API in node js to get data with multiple queries with my sql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related