Bookshelfjs where clause on join

Question

I just started using bookshelfJS, and I couldn't find a clear answer on how to do the following.

Consider I have the following tables:

product
--------
id


product_account
--------
id
product_id
account_id


collection
--------
id


collection_product_account
--------
collection_id
product_account_id

There can be many products to a collection.

I want to do the following

SELECT pa.*, p.* FROM product_account pa 
INNER JOIN collection_product_account cp ON cp.product_account_id = pa.id 
INNER JOIN product p ON p.product_account_id = pa.product_id 
WHERE cp.collection_id = ?

How would I be able to pass in a collection id, and return a whole list of product_accounts, and then get products from that?

For reference, I am doing this if I were querying with a product_account_id

new productAccountModel()
.where({account_id: 1})
.fetchAll({withRelated: ['product']})
.then(function(productAccounts)
{
  return productAccounts.toJSON());
});

Answer 1

I'm assuming this is your model:

var Product = bookshelf.Model.extend({
    tableName: 'product',
    collections: function() {
        return this.belongsToMany(Collection);
    }
});

var Collection = bookshelf.Model.extend({
    tableName: 'collection',
    products: function() {
        return this.belongsToMany(Product);
    }
});

Then you have to switch the logic a bit. Once you say new Product(), you can't query this by related table. But you can switch it, like this:

new Collection({id: 1}).fetch({
    withRelated: ['products']
}).then(function(result) {
    res.json(result.toJSON());
});

Does this help?

Update:

If necessary, you can additionally attach relations from the model you are attaching, i.e.:

new Collection({id: 1}).fetch({
    withRelated: ['products.company']
}).then(function(result) {
    res.json(result.toJSON());
});