How to use initialSorting feature in DataGrid with oneHasMany field?

[kausik10]

Hi, I have a Record model which contains files field defined as

• files = c.oneHasMany(File, "record");

I need to display the Record in a DataGrid in such a way that Records are displayed in descending order on the basis of total number of files for that particular Record.

So, for example record ABC contains 4 files and record BCD contains 10 files. My DataGrid should always be listed in the order:

1. BCD
2. ABC

The initialSorting allows to sort stringColumn or uuidColumn as per the documentation I found.

Or at max, I have achieved using initialSorting in this way

• initialSorting={!certain_field ? { 'certain_field': 'asc' } : { 'certain_field': 'asc' }}

Could you help me find a feasible solution for the purpose? Thank you!

[jonasnobile]

Hi,

Contember doesn’t support aggregate fields directly at this time, so to sort Records by the number of files they contain, you’ll need to calculate the file count separately. One effective approach is to use a custom SQL view. This view will compute the count of files for each record, which you can then reference in your model.

For example, you could modify your models as follows:

export class Record {
	// ... other fields
	files = c.oneHasMany(File, 'record')
	stats = c.oneHasOneInverse(RecordStats, 'record').notNull()
}

@c.View(`
	SELECT
		-- Data binding requires an id for each entity; here we use Record's id to keep it consistent.
		r.id AS id,
		(SELECT count(*) FROM files WHERE record_id = r.id) AS count,
		r.id AS record_id
	FROM record AS r
`)
export class RecordStats {
	// Ensure that the relation points back to the Record model
	record = c.oneHasOne(Record, 'stats')
	
	count = c.intColumn().notNull()
}

With this setup, you now have a count field in your RecordStats view, which represents the total number of files per record. You can then use the initialSorting property in your DataGrid to sort by this count in descending order:

initialSorting={{ 'stats.count': 'desc' }}

This configuration will ensure that Records are displayed in descending order based on their file counts (e.g., a record with 10 files will appear before one with 4 files).

I hope this helps clarify the solution. Let me know if you have any further questions or need additional details!