Sort a table based on a child relationship field

Is there only 1 child record?

Yes only one child record.

I am trying to do it through J/S like so:

skuid.$M(‘MyModel’).orderByClause = ‘Child_Relations__r.Date__c DESC’;

Not sure that would work since child record fields aren’t supported in SOQL where clause.

You are correct it is not working.

I haven’t tried this, but it may be worth testing out to see if it will work.

Setup a UI-only field to reference the field in the subquery you’re interested in on the main model field list. Then use the action framework to sort model client side. The UI field should also be able to be used for sorts in a table.

Thanks for the idea John. I tried accessing the child relationship via the Ui-Only field with “Child_Relations__r.Date__c” but it doesn’t display the data. 

I’ve done this before - it should work. You can use merge values to bring child relationship data into formula fields, and yes, to John’s point, client-side sorting should work fine. There are some examples of the syntax here: https://docs.skuid.com/latest/en/skuid/merge-syntax/#looping-over-arrays

You have to reference the record and index of the child relationship.  It should read 
{{Child_Relations__c.records.0.Date__c}}.  

Thanks for the help guys! So if I put this merge syntax in a template it displays data
{{#Abel_Supplier_Catalogues__r.records}}
{{Catalog_Item_Count__c}}
{{/Abel_Supplier_Catalogues__r.records}}

If I put that same merge syntax in a UI Only field it doesn’t work. 

Also, I think our Skuid version may be to old as I don’t have a sorting sequence option.

The syntax you have there is for conditionally displaying a field, which I believe is a different use case from what you’re looking for.  Setup the UI field with the syntax from my last response with the index number and the field name in the merge.  The use a model field rather than a template.  

Also in later versions of Skuid, you can tie a template’s sort order to a field in a model, but you still need the field defined to run the sort.  In this case it’s cleaner to use the UI model.

Thanks this syntax worked: {{Child_Relations__r.records.0.Date__c}}.  Still trying to figure out the sorting issue, if possible. Thought I could use JavaScript on the UI only field. No go so far.

You can for sure use Dlrs to concat the value from the related record. That way you can use standard sort functionality in the model.

Yeah I know but I was trying to not have another DLRS job on this object.

So it looks like (at least on my version of Skuid), you can’t sort by UI fields in a table.  This is likely because sorting will re-query the database so you get refreshed records of interest.

However I did just setup an action sequence that was able to successfully sort by a UI field and it was really fast.  So this is totally possible with an Action, but it looks like you might need to get creative on the UI.

A couple thoughts: first, you can use an event driven action sequence to run the sort on page load, then everything will load in the correct order.  Second, If you want to have the user drive the sort you can fire the action off of button(s).

One important note here, sorting on the client side will not go back to the database to retrieve a refreshed set of records.  Also, as Pat mentioned earlier, you can’t sort on a subquery in SOQL.  What I suggest is to use a subquery condition to scope your data in alignment to what you’re sorting on.  If you do client side sorting, you need to make sure you have all the records pulled down to the browser or the user will be interacting with inconsistent data.

You can also use PB & Flow to set the value. Assuming you don’t have too many records for this. You can also schedule to the flow to run once in order to get the values on the parent record.

One last thought.  You can use an aggregate query to do your sort and align the data scope, then use the results of the aggregate as the filter for you main model.  This won’t help with the UI, but it will at least get you an ordered list back that aligns to the data. 

For example, you could run an aggregate query on the Child_Relation__c table, aggregate by the ParentId and order by the Max(Date__c).  Then your interactive model would be filtered by field from another model and use the aggregate query results.

Guys thanks for all the suggestions. I ended up going about it a little differently because of the requirements.

So, there is a master list that the person can sort but Item count.

So I have two tabs the “Master” list and “Account” list (which I was trying to get the sorting to work on). 

So when they sort the “Master” list. I put a button on the “Account” list that will update the list with only the Accounts from the “Master” list.