I have two tables I am querying with bookshelfjs using 'withRelated'. How do I sort the results by a column in the related table? For example I want to order all the results by rankingLinks.id
.fetchAll({withRelated: ['tickerSymbolLinks.tickerSymbol', 'rankingLinks', 'logoLinks.logo']})
Seems simple but banging my head against the wall on this one...
You can actually order stuff by withRelated, however, it will only order the result within this specific property. I.e.:
new Participant().query(function(qb) {
qb.orderBy('participants.id', 'desc'); // You don't have access to any "withRelated" models
}).fetchAll({
withRelated: [{
'courses': function(qb) {
qb.orderBy('courses.id', 'desc');
}
}]
}).then(function(result) {
res.json(result.toJSON());
});
This code returns all participants, order by their id (descending). Within this result, I order courses by courses.id desc. The result is as follows:
[
{
"id": 3,
"name": "Susan Doe",
"created_at": 1429685077496,
"updated_at": 1429685077496,
"courses": [
{
"id": 3,
"course_name": "Advanced JavaScript",
"duration": 80,
"_pivot_participant_id": 3,
"_pivot_course_id": 3,
"_pivot_level": null,
"_pivot_grade": null,
"_pivot_date_of_attendance": null
},
{
"id": 1,
"course_name": "iOS development",
"duration": 120,
"_pivot_participant_id": 3,
"_pivot_course_id": 1,
"_pivot_level": null,
"_pivot_grade": null,
"_pivot_date_of_attendance": null
}
]
},
{
"id": 2,
"name": "Richard Doe",
"created_at": 1429685077496,
"updated_at": 1429685077496,
"courses": [
{
"id": 2,
"course_name": "Android development",
"duration": 60,
"_pivot_participant_id": 2,
"_pivot_course_id": 2,
"_pivot_level": null,
"_pivot_grade": null,
"_pivot_date_of_attendance": null
},
{
"id": 1,
"course_name": "iOS development",
"duration": 120,
"_pivot_participant_id": 2,
"_pivot_course_id": 1,
"_pivot_level": null,
"_pivot_grade": null,
"_pivot_date_of_attendance": null
}
]
},
{
"id": 1,
"name": "John Doe",
"created_at": 1429685077495,
"updated_at": 1429685077495,
"courses": [
{
"id": 3,
"course_name": "Advanced JavaScript",
"duration": 80,
"_pivot_participant_id": 1,
"_pivot_course_id": 3,
"_pivot_level": null,
"_pivot_grade": null,
"_pivot_date_of_attendance": null
},
{
"id": 1,
"course_name": "iOS development",
"duration": 120,
"_pivot_participant_id": 1,
"_pivot_course_id": 1,
"_pivot_level": null,
"_pivot_grade": null,
"_pivot_date_of_attendance": null
}
]
}
]
Now if I'd like to order the results by courses, I'd have to change the query a bit. I.e.:
new Course().query(function(qb) {
qb.orderBy('courses.id', 'desc');
}).fetchAll({
withRelated: ['participants']
}).then(function(result) {
res.json(result.toJSON());
});
This produces a slightly different result, but with desired ordering:
[
{
"id": 3,
"course_name": "Advanced JavaScript",
"duration": 80,
"participants": [
{
"id": 1,
"name": "John Doe",
"created_at": 1429685077495,
"updated_at": 1429685077495,
"_pivot_course_id": 3,
"_pivot_participant_id": 1,
"_pivot_level": null,
"_pivot_grade": null,
"_pivot_date_of_attendance": null
},
{
"id": 3,
"name": "Susan Doe",
"created_at": 1429685077496,
"updated_at": 1429685077496,
"_pivot_course_id": 3,
"_pivot_participant_id": 3,
"_pivot_level": null,
"_pivot_grade": null,
"_pivot_date_of_attendance": null
}
]
},
{
"id": 2,
"course_name": "Android development",
"duration": 60,
"participants": [
{
"id": 2,
"name": "Richard Doe",
"created_at": 1429685077496,
"updated_at": 1429685077496,
"_pivot_course_id": 2,
"_pivot_participant_id": 2,
"_pivot_level": null,
"_pivot_grade": null,
"_pivot_date_of_attendance": null
}
]
},
{
"id": 1,
"course_name": "iOS development",
"duration": 120,
"participants": [
{
"id": 1,
"name": "John Doe",
"created_at": 1429685077495,
"updated_at": 1429685077495,
"_pivot_course_id": 1,
"_pivot_participant_id": 1,
"_pivot_level": null,
"_pivot_grade": null,
"_pivot_date_of_attendance": null
},
{
"id": 2,
"name": "Richard Doe",
"created_at": 1429685077496,
"updated_at": 1429685077496,
"_pivot_course_id": 1,
"_pivot_participant_id": 2,
"_pivot_level": null,
"_pivot_grade": null,
"_pivot_date_of_attendance": null
},
{
"id": 3,
"name": "Susan Doe",
"created_at": 1429685077496,
"updated_at": 1429685077496,
"_pivot_course_id": 1,
"_pivot_participant_id": 3,
"_pivot_level": null,
"_pivot_grade": null,
"_pivot_date_of_attendance": null
}
]
}
]
I hope this helps a bit!
