Advanced Query Sorting

General Q&A
1

I am trying to sort a query (or an array) by the names of a related object and display them in an object-table in this order. Let’s call the object A and the related object B. For some A objects, it does not have a B relationship. If A.B == null, these should go at the top, else I want to sort on A.B.name alphabetically.

I don’t think this can be accomplished with an orderBy on the query, so I’m trying to get back an array and then use Array.sort(). After messing with this a while and outputting the whole process to the console, it looks like the object-table is simply not respecting the sort order when rendering the array. Any suggestions on how to best accomplish this?

2

Hi Fletcher

The problem you are running into has to do with the synchronous execution of the App JS despite some functions actually being asynchronous, in this case .sort() combined with referencing a related object. The related object reference inside of the sort function is executing asynchronously (ie object.relationship_name() is technically an async function) but sort is not waiting for it to complete before evaluating your sort logic.

To solve this you need to first fetch and cache all relevant related objects so that you can access their values inside the sort function without having to call them with an asynchronous function.

Something like this. Let’s assume we want to sort our purchase orders by the name of the related customer.

function sortArray() {
    view.sorted_array = DB.purchase_request.all().toArray();

    var customers = DB.customer.all().toArray();
    var customerLookup = {}
    customers.forEach(function (c) {
        customerLookup[c.id] = c;
    })
    view.sorted_array = view.sorted_array.sort(function (a, b) {
        if (customerLookup[a.customer_id].name > customerLookup[b.customer_id].name) {
            return 1;
        } else if (customerLookup[a.customer_id].name < customerLookup[b.customer_id].name) {
            return -1;
        } else {
            return 0;
        }
    })
}
3

Thanks Tielman, your suggestion makes sense and I believe I’ve implemented it, but the table is still displaying out of order

view.survey_deficiencies = view.QCE_Survey.survey_deficiencies.toArray();
var sdLookups = {};
for (var i=0; i<view.survey_deficiencies.length; i++) {
    var sd = view.survey_deficiencies[i];
    sdLookups[sd.id] = sd.survey_device_id ? sd.survey_device().QCE_Device_DeviceName : "0";
}
console.log("sdlookups: " + JSON.stringify(sdLookups));
console.log("before sorting: " + JSON.stringify(view.survey_deficiencies))
view.survey_deficiencies.sort(function (a, b) {
    var a_sdName = sdLookups[a.id];
    var b_sdName = sdLookups[b.id];
    if (a_sdName < b_sdName) {
        return -1;
    }
    else if (a_sdName > b_sdName) {
        return 1;
    }
    else {
        return 0;
    }
});
console.log("AFTER sorting: " + JSON.stringify(view.survey_deficiencies))

The AFTER Sorting log entry prints the array sorted how I would expect, but it is not rendered this way in the object-table. The code is wrapped in a function called on both init and resume for the view.

4

Confirmed offline that the above sort function does work, the problem for it not displaying correctly was in the view XML

1 Like
5

AKA user error :slight_smile:

1 Like