Sorting related records

[treagod]

What possibilities are there to sort related records? I have a List and Item. The Item has a position field which determines the order to display. How could I sort the items with a query instead of doing it in my Crystal code?

list = List.get!(id: 1)
list.items.order(:position) # Does not have any effect on the items

# Storing in a seperated variable does the trick
# If used in a template we have to add it manually to the context
ordered_items = list.items.order(:position) 

This issue seems to be more prominent if using prefetch:

lists = List.prefetch(:items)
# No db connection yet
lists.each do |list|
  # All items are prefetched
  list.items.order(:position) # Does not have any effect
end

Would a #order! make sense, where the queryset is changed in place instead of returning a new queryset? The same question also applies to other query methods

[ellmetha]

I am curious: how is this relationship between List and Item defined? Is it a many-to-many field?

In any case, by design, query set methods never mutate existing query set objects and always produce new ones. Query sets in Marten are immutable. This aspect will never change for several reasons:

• Query optimization: altering the order of already executed query sets is tricky and would lead to performance issues due to the need to re-execute queries or invalidate cached record entries.
• Consistency and predictability: the fact that query sets are immutable ensures that these will never change unexpectedly, which makes the code easier to reason about. This also reduces the likelihood of encountering bugs induced by the fact that the code is unexpectedly mutating some query sets.

Given this, an #order! method is not an option!

The #order method produces a new query set object (like most of the other query set methods). If one wants to retain the order produced by calling this method, the simplest is really to assign the obtained query set to a new variable.

[treagod]

Many to one: zero or more Item belong to a List in my case. Assigning the ordered items to its own custom context variable is fine for me in case of a detail handler and the concept of immutability makes sense!

But how would I access the ordered items in case of a list handler? I.e. I'm displaying all List records with all ordered Item associations, preferably after prefetching the items

[ellmetha]

Unfortunately, this is not possible presently. In the case of a list of items, the only way to override the ordering right now would be to add a custom method that applies some custom ordering logic in Crystal in the model defining the relation. Alternatively, you could also construct an array containing tuples with the List objects and the associated Item list properly ordered, and then iterate on that in your templates.

Long term, in order to support this we would need something else: the ability to configure prefetched records and the associated query set to use when prefetching records. Eg:

List.prefetch(:items, query_set: Item.order(:position))

EDIT: tracked in #264.