How to use 2 different target ids in bookshelf.js relationships?

47 Views Asked by At

I have 2 tables, user and friend, with the following data:

user

id  | name      | gender
----------------------------
1   | John      | male
2   | Jessica   | female
3   | Bryan     | male
4   | Mark      | male
5   | Jane      | female

friend

id  | user1 | user2
-------------------------
1   | 1     | 3
2   | 1     | 4
3   | 2     | 1
4   | 2     | 4
5   | 2     | 5
6   | 5     | 1
7   | 4     | 2

How can I get the following result using Bookshelf.js relations when I query a user:

"data" : {
    "name": "John",
    "gender": "male",
    "friends" : [
        {
            "id": 3,
            "name": "Bryan",
            "gender": "male"
        },
        {
            "id": 5,
            "name": "Jane",
            "gender": "female"
        },
        {
            "id": 2,
            "name": "Jessica",
            "gender": "female"
        },
        {
            "id": 4,
            "name": "Mark",
            "gender": "male"
        }
    ]
}

Please note that the friends list is in alphabetical order.

I tried Model.belongsToMany relationship of bookshelf.js but I only get:

"data" : {
    "name": "John",
    "gender": "male",
    "friends" : [
        {
            "id": 3,
            "name": "Bryan",
            "gender": "male"
        },
        {
            "id": 4,
            "name": "Mark",
            "gender": "male"
        }
    ]
}

Basically, I want to include in my query both the user1 and user2 columns of friend table but I dont know how to. I would like to include Jessica and Jane in the friends list if possible.

Here's my existing codes:

var User = Bookshelf.Model.extend({
  tableName: 'user'
  friends: function() {
    return this.belongsToMany('User', 'friend', 'user1', 'user2');
  }
});

module.exports =  Bookshelf.model('User', User);

var Friend = Bookshelf.Model.extend({
  tableName: 'friend'
});

module.exports =  Bookshelf.model('Friend', Friend);

// Router

router.get('/users/:id', function(req, res) {
  User.where({
    id: req.params.id
  }).fetch({
    withRelated: ['friends']
  }).then(function(user) {
    res.json({
      success: true,
      data: {
        name: user.get('name'),
        gender: user.get('gender'),
        friends: user.related('friends')
      }
    });
  }).catch(function(err) {
    var message = 'Error: ' + err.message;
    res.json({
      success: false,
      message: message
    });
  });
});

Thank you.

0

There are 0 best solutions below