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.