using bookshelfjs to order results by a related table column

Question

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...

Answer 1

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!