Kousha Kousha - 3 months ago 16
Node.js Question

BookshelfJS limiting withRelated in FetchAll doesn't work

I have a

hasMany
to
belongsTo
relation between
boxes
and
box_states
.

I want to grab all my boxes with their latest state, so this is my query:

new Box()
.orderBy('id', 'ASC')
.fetchAll({
withRelated: [{
'states' : function(qb) {
qb.limit(1)
.orderBy('id', 'DESC');
}
}]
})
.then(function (boxes) {
res.json(boxes);
});


This returns all the boxes, but only the last box has one relation; everything has no relation.

Help would be appreciated.

Answer

Well, this works pretty much as expected. If you debug your solution, you'll see something like this (I'm giving you my example, which has the same relation as yours - one town, many addresses):

select "addresses".* from "addresses" where "addresses"."town_id" in (1, 2, 3, 4) group by "id" limit 1

What this does is basically select just ONE(!) related model (address) for ALL instances (the addresses.town_id in (1,2,3,4) segment).

So basically what you need to do is foreach the main results and go withRelated upon each.

new Town().fetchAll().then(function(towns) {

    var my_towns_with_only_last_address = [];

    Promise.map(towns.toJSON(), function(town) {
        return new Town({
            id: town.id
        }).fetch({
            withRelated: [{
                'address': function(qb) {
                    qb.limit(1);
                    qb.orderBy('id', 'desc');
                }
            }]
        }).then(function(result) {
            return my_towns_with_only_last_address.push(result.toJSON());               
        });
    }).then(function() {
        res.json(my_towns_with_only_last_address);
    });

});

I'm not sure if there are any better options... this works.