CODEWITHSUNDEEP
javascriptnode.jsbookshelf.js
aWebDeveloper
aWebDeveloper

Reputation: 38352

Join in bookshelf.js

How do i achieve the following relation in bookshelf.js

SELECT user_accounts.user_id,  `event_id` 
FROM  `calendar_events` 
JOIN user_accounts ON user_accounts.user_id = calendar_events.`created_by` 
LIMIT 10

My Model

var CalendarEvent = bookshelf.Model.extend({
    tableName: 'calendar_events',
    hasTimestamps: ['created_on'],
    user: function() {
        return this.hasOne('UserAccount','user_id');
    }
});

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

Upvotes: 1

Views: 4129

Answers (2)

Eric Ly
Eric Ly

Reputation: 2133

I suppose you found the solution since 2016 but here is the answer

var CalendarEvent = bookshelf.Model.extend({
    tableName: 'calendar_events',
    hasTimestamps: ['created_on'],
    user: function() {
        return this.belongsTo(UserAccount, 'user_id', 'created_by');
    }
});

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

Use belongsTo() method because the foreign key is in the CalendarEvent Model and not in the UserAccount model. Append the name of the foreign key to belongsTo() parameters to specify the name of the foreign key.

Then, use the Bookshelf model like this :

CalendarEvent
    .forge()
    .fetchPage({ pageSize: 10, withRelated: ['user'] })
    .then(data => {
        // Data retrieval
    })
    .catch(exc => {
        // Error management
    });

The fetchPage method uses page and pageSize to create a pagination. You will retrieve a data.pagination object containing rows info (number to rows, page number, page size, page total count) and data.toJSON() will retrieve your models.

Upvotes: 0

jkusachi
jkusachi

Reputation: 806

If you wanted to get that exact style query, you could use the knex query builder and try to build a query to match your needs

have not tested this but should be something like this

CalendarEvent.forge().query(function(qb){
  qb.join('user_accounts', 'user_accounts.user_id', '=', 'calendar_events.created_by');
  //qb.where() //if you wanted
  //qb.andWhere();  //and if you wanted more
  qb.limit(10);

})    
.fetchAll();

(you dont need the .where or .andWhere, just added those for fun)

It might be possible to do it purely in bookshelf, but i'm not sure of how at the moment.

Upvotes: 3

Related Questions