Node JS - express - postgres obtain nested json objects by loop queries

Question

I am trying following code at node js using postgres to obtain a list of nested json object like this:

{
    "elements": [
        {
            "name": "element 1",
            "description": "lorem ipsus",
            "docs": [
                {
                    "docs_id": 1053,
                    "docs_file": "../uploads/1461s444---planimetria.pdf",
                    "docs_created_at": "ThuMay14201506: 00: 00GMT+0200(CEST)"
                },
                {
                    "docs_id": 1054,
                    "docs_file": "../uploads/1461s444---VAX-with-highlight.pdf",
                    "docs_created_at": "ThuMay14201506: 00: 00GMT+0200(CEST)"
                },
                {
                    "docs_id": 1055,
                    "docs_file": "../uploads/1461s444---Elaborato-Planimetrico-with-highlight.pdf",
                    "docs_created_at": "ThuMay14201506: 00: 00GMT+0200(CEST)"
                }
            ]
        },
        {
            "name": "element 2",
            "description": "lorem ipsus",
            "docs": [
                {
                    "docs_id": 1056,
                    "docs_file": "../uploads/pianta.pdf",
                    "docs_created_at": "ThuMay17201106: 00: 00GMT+0200(CEST)"
                },
                {
                    "docs_id": 1055,
                    "docs_file": "../uploads/test.pdf",
                    "docs_created_at": "ThuMay16201706: 00: 00GMT+0200(CEST)"
                }
            ]
        }
    ]
}

using this code:

apiRoutes.get('/elements', function(req, res) {

var elements = [];  
knex.select().table('element').where(req.query)
    .then (function(rows){
        for(var i in rows){
            var element =  rows[i];
            knex.select().table('document').where('element_id',rows[i].element_id)
                .then (function(docs){
                    element['docs'] = docs;
                    //console.log (elements);
                    elements.push(element);
                });
        }
        res.json (elements);
    });
});

but the result is an empty array.

I think it's a problem with the asynchronous processing about the queries but I can't solve it.

Community · Accepted Answer · 2017-05-23 10:27:15Z

1

Your for loop is making a bunch of async calls. You need to return all of them in a promise. Here's an example using bluebird. (I didn't test this yet.)

var Promise = require('bluebird')

knex.select().table('element').where(req.query)
    .then (function(rows){

        var promises = rows.map(function(element){
            return knex.select().table('document').where('element_id',element.element_id)
                .then(function(docs){
                    element['docs'] = docs

                    return element;
                });
        })

       return Promise.all(promises)     
    }).then(function(elements){     
        res.json (elements);
    })
});

However, I think issuing the second query is an n+1. Knex supports a left outer join. You should use that.

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Nov 6, 2015 at 15:23

Josh C.

4,3935 gold badges33 silver badges52 bronze badges

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

2 Comments

Jacopo Mugnaini Over a year ago

WORKS, Thank you Josh!!! An other question... What's the best way if i would process an other subquery like "document" inside the main query "element"

Josh C. Over a year ago

i think you mean to use a left outer join. That may be better for a separate question on Stackoveflow.

Collectives™ on Stack Overflow

Node JS - express - postgres obtain nested json objects by loop queries

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related