CODEWITHSUNDEEP
mysqlnode.jsbookshelf.js
anand
anand

Reputation: 1751

How to Join two tables in bookshelfjs in Node.js

I Have two tables in MySQl DB which are:

Customer:

  • cust_ID (PK)
  • cust_name
  • trans_ID (FK)

Transaction

  • trans_id (PK)
  • trans_amount

In Node.js I have created the two models for both of these tables , Now i want to do Inner Join on both these table based on trans_id. I am not getting the Idea to how to do.

var Transaction = bookshelf.Model.extend({
    tableName: 'Transaction'
});

var Customer = bookshelf.Model.extend({
   tableName: 'Customer'
});

Upvotes: 2

Views: 3487

Answers (1)

Kalle Björklid
Kalle Björklid

Reputation: 655

I'm a bookshelf.js beginner myself, but if I'm not mistaken, bookshelf.js abstracts away the notion of inner joins. Instead, if your question can be translated as 'how do I get a transaction/transactions and their related customers?' the answer would be something like this:

transaction.js:

var Transaction = bookshelf.Model.extend({
    tableName: 'Transaction',
    customers: function() {
        return this.hasMany(Customer, 'trans_ID');
    }
});

customer.js:

var Customer = bookshelf.Model.extend({
   tableName: 'Customer',
   transaction: function() {
       return this.belongsTo(Transaction, 'trans_ID');
   }
});

To get a transaction with all its related customers you do something like this:

new Transaction()
    .where('trans_id', 1)
    .fetch({withRelated: ['customers']})
    .then(function(theTransaction) {
       var arrayOfCustomers = theTransaction.customers;
       //...
    });

Please see bookshelf.js's documentation for hasMany and belongsTo for further information.

I hope this answer was in the ballpark of what you were looking for.

Upvotes: 3

Related Questions