Is there a way to use "orderBy()" with belonging models?

General Q&A
1

I have a model that has several belongs-to fields. Is there a way to order by a field in the belongs to model? For example:

<model name="model_1" label="Model 1">
    <belongs-to model="model_2" />
    <belongs-to model="model_3" />
    <belongs-to model="model_4" />
</model>

I want to be able to do something like the following:

view.order = DB.model_1.all().include('model_2', 'model_3', 'model_4').orderBy('model_2.name').toArray()

I'm wanting to do this to sort the query based off of a nested key/value pair (the model_2 name). Is this possible? Or should I grab the original query (without the orderBy()) and sort the query from there?

2

It is not currently possible to order a query off of a field on a related object.

Your options are firstly, as you have suggested, to do the query without a sort order, turn it into an array, and then manually sort the array as you see fit afterwards.

PLEASE NOTE: In order to actually sort on a field on a related object you need to cache all the potential related objects first and then reference the cached objects in your sort function. If you try to reference the related object in the sort function using normal rel_name() syntax it won't work. Firstly, it changes the sort into an asynchronous operation which is not supported in the view JS, and secondly, depending on the number of objects you want to sort through, it would make the sort function very inefficient as it would make n x 2 number of DB lo

Your second option is to denormalize your data to include the field you want to order by on the model in question and then order the query as usual. So in your example that would mean including a field for model_2.name on model_1.

1 Like
3

That’s what I figured. Thanks for the help @TielmanleRoux !

4

What does ‘cache’ mean in this context?

5

@chansmann Sorry for the late response, I only now see you asked the follow-up question.

Basically, caching here means retrieving the related objects and storing them in a variable that you can access directly in the sort function so that you don’t need to make a DB call in order to get their values.

Look at this answer for an example.

I hope this helps