3 ms·

"Say now we want to display the album list sorted by album’s duration, shortest first." How would we do this in an ORM like ActiveRecord or Django ORM, without

"Say now we want to display the album list sorted by album’s duration, shortest first." How would we do this in an ORM like ActiveRecord or Django ORM, without generating multiple queries?

strken 4y ago
I haven't used Rails or ActiveRecord for a long time, but from memory it gives you an escape hatch in the form of Arel that lets you represent parts of your query as SQL while still using the ORM for the rest. Edit: I think it's something like Album .select(:id, :name, "SUM(songs.length) AS total_length") .left_outer_joins(:songs) .group(:id) .order("SUM(songs.length) ASC") I think you don't even need to explicitly wrap it in Arel.
- kayodelycaon 4y ago
  I use rails every day. Looks close enough. You’d just need to specify the fields with the table name, I.e. ‘albums.id’. Also, for anyone reading, a total_length method dynamically will be added objects returned. :)
- slimdave 4y ago
lmm 4y ago
I don't know about ActiveRecord or Django specifically, but: the obvious way in their query builder? Adding a joined collection, doing an aggregation on it, and sorting by it, are all easy in any ORM worth bothering with. What did you try and where did you get stuck?
deleted 4y ago
[deleted]
ColonelPhantom 4y ago
In Django, you would write an annotation as documented at https://docs.djangoproject.com/en/4.1/topics/db/aggregation/#generating-aggregates-for-each-item-in-a-queryset https://docs.djangoproject.com/en/4.1/topics/db/aggregation/... So something like Album.objects.annotate(duration=Sum('track__length')).order_by('duration') There are also libraries that enable you to define an annotation or aggregation inside a model, that you can then get with a call like select_properties, similar to the built-in select_related (which you use to get a foreign key in one query) and prefetch_related (which is select_related for many-to-many fields).
- rahimnathwani 4y ago
  Thanks. I haven't used Django for ages, but do recall using annotate() in the past. At that time, I didn't need to worry about performance, so didn't look into what happens under the hood, i.e. whether the call generates more than one SQL query.

"Say now we want to display the album list sorted by album’s duration, shortest first." How would we do this in an ORM like ActiveRecord or Django ORM, without generating multiple queries?

strken 4y ago

I haven't used Rails or ActiveRecord for a long time, but from memory it gives you an escape hatch in the form of Arel that lets you represent parts of your query as SQL while still using the ORM for the rest. Edit: I think it's something like Album .select(:id, :name, "SUM(songs.length) AS total_length") .left_outer_joins(:songs) .group(:id) .order("SUM(songs.length) ASC") I think you don't even need to explicitly wrap it in Arel.

kayodelycaon 4y ago
I use rails every day. Looks close enough. You’d just need to specify the fields with the table name, I.e. ‘albums.id’. Also, for anyone reading, a total_length method dynamically will be added objects returned. :)
slimdave 4y ago

lmm 4y ago

I don't know about ActiveRecord or Django specifically, but: the obvious way in their query builder? Adding a joined collection, doing an aggregation on it, and sorting by it, are all easy in any ORM worth bothering with. What did you try and where did you get stuck?

deleted 4y ago

[deleted]

ColonelPhantom 4y ago

In Django, you would write an annotation as documented at https://docs.djangoproject.com/en/4.1/topics/db/aggregation/#generating-aggregates-for-each-item-in-a-queryset https://docs.djangoproject.com/en/4.1/topics/db/aggregation/... So something like Album.objects.annotate(duration=Sum('track__length')).order_by('duration') There are also libraries that enable you to define an annotation or aggregation inside a model, that you can then get with a call like select_properties, similar to the built-in select_related (which you use to get a foreign key in one query) and prefetch_related (which is select_related for many-to-many fields).

rahimnathwani 4y ago
Thanks. I haven't used Django for ages, but do recall using annotate() in the past. At that time, I didn't need to worry about performance, so didn't look into what happens under the hood, i.e. whether the call generates more than one SQL query.