Bookshelf.js query on related table row

Question

I'm starting using Node.JS, bookshelf.js and bookshelf-pagemaker.

My database contain 2 tables : - Asset with 3 main rows (idasset, name, idarrangement) - Arrangement_details with 2 main row (idarrangement_details, material)

I would like get all Asset where material = 2 for example.

The Asset model :

var AssetsCollection = DB.Model.extend({
    tableName: 'assets',
    idAttribute: 'idassets',
    arrangementdetails: function () {
        return this.belongsTo(ArrangementDetail, 'idarrangement_details');
    }
});

I've tried this code but it crash because arrangementdetails is not joined.

     var pm = require('bookshelf-pagemaker')(DB);    
        pm(AssetsCollection.AssetsCollection)
            .forge()
            .limit(req.params.limit)
            .offset(req.params.page)
            .query(function(qb){
                qb.where('Arrangement_details.material', '=', 4)
            })
            .paginate({request: req, withRelated: ['arrangementdetails'])
            .end({})
            .then(function (results) {
                callback(null, {code: 200 , res: results});
            });

It's possible to do that ?

Regards, DarKou

Answer 1

you need to join the tables in order to search a related one. without seeing your table structure this will be somewhat a shot in the dark, but you should use a join in the query callback along with your where similar to the following. to be clear there is no functional issue with either bookshelf or pagemaker with what you are trying to accomplish.

it appears you have 2 tables, with the following

table: assets, idAttribute: idassets
table: Arrangement_details, idAttribute: idarrangement_details

var pm = require('bookshelf-pagemaker')(DB);
pm(AssetsCollection.AssetsCollection)
.forge()
//.limit(req.params.limit) -- not needed as you are already passing req to paginate
//.offset(req.params.page) -- not needed as you are already passing req to paginate
.query(function(qb){
    qb.join('Arrangement_details', 'Arrangement_details.idarrangement_details', '=', 'assets.idarrangement_details')
    .where('Arrangement_details.material', '=', 4);
})
.paginate({request: req, withRelated: ['arrangementdetails'])
.end()
.then(function (results) {
    callback(null, {code: 200 , res: results});
});

i would however encourage you use less confusing names for your tables/fields similar like the following where id is the idAttribute for both and details_id is the foreign key for the belongsTo relationship

var AssetsCollection = DB.Model.extend({
    tableName: 'assets',
    idAttribute: 'id',
    arrangementDetails: function () {
        return this.belongsTo(ArrangementDetail, 'details_id');
    }
});
var ArrangementDetail = DB.Model.extend({
    tableName: 'arrangement_details',
    idAttribute: 'id'
});

var pm = require('bookshelf-pagemaker')(DB);
pm(AssetsCollection.AssetsCollection)
.forge()
.query(function(qb){
    qb.join('arrangement_details', 'arrangement_details.id', '=', 'assets.details_id')
    .where('arrangement_details.material', '=', 4);
})
.paginate({request: req, withRelated: ['arrangementDetails'])
.end()
.then(function (results) {
    callback(null, {code: 200 , res: results});
});