Enric Ayguadé Enric Ayguadé - 2 months ago 10
Node.js Question

How do you query data with Bookshelf with a lot of relations?

So, I have the following:

var Device = services.bookshelf.Model.extend({
tableName: 'device',
calendar: function() {
return this.belongsToMany(Calendar, 'calendar_device', 'deviceId', 'calendarId');
}
});

var Calendar = module.exports = services.bookshelf.Model.extend({
tableName: 'calendar',
device: function() {
return this.belongsToMany(Device, 'calendar_device', 'calendarId', 'deviceId');
},
schedule: function() {
return this.hasMany(Schedule);
}
});

var Schedule = module.exports = services.bookshelf.Model.extend({
tableName: 'schedule',
calendar: function() {
return this.belongsTo(Calendar);
},
channel: function() {
return this.hasMany(Channel);
}
});

var Channel = module.exports = services.bookshelf.Model.extend({
tableName: 'channel',
schedule: function() {
return this.belongsTo(Schedule);
},
screenItem: function() {
return this.hasMany(ScreenItem);
}
});


And the relations keep going...
How am I supposed to make a query to get, for example, the channels of a device? I don't know if I'm missing something but y I haven't found much information on this...

Answer
Device.where('id', id).fetch({withRelated: ['calendar.schedule.channel']}).then(function(devices) {
    ...
})

What I do is to fetch the Device with 'id' = id specifying the relations of the model that I want to return to (that are declared as it can be seen in the models declaration in my question). So, saying:

  • {withRelated: ['calendar']} returns a Device with its Calendars
  • {withRelated: ['calendar.schedule']} returns a Device with its Calendars with its Schedules
  • {withRelated: ['calendar.schedule.channel']} returns a Device with its Calendars with its Schedules with its Channels

and so on if there are more related models you want to get.

The response is something like:

{
    id: 1,
    ...,
    calendars: [
        id: 1,
        ...,
        schedules: [
            ...
        ]
    ]
}