Efficient way to select one from each category - Rails

Question

I'm developing a simple app to return a random selection of exercises, one for each bodypart.

bodypart is an indexed enum column on an Exercise model. DB is PostgreSQL.

The below achieves the result I want, but feels horribly inefficient (hitting the db once for every bodypart):

BODYPARTS = %w(legs core chest back shoulders).freeze

@exercises = BODYPARTS.map do |bp|
  Exercise.public_send(bp).sample
end.shuffle

So, this gives a random exercise for each bodypart, and mixes up the order at the end.

I could also store all exercises in memory and select from them; however, I imagine this would scale horribly (there are only a dozen or so seed records at present).

@exercises = Exercise.all

BODYPARTS.map do |bp|
  @exercises.select { |e| e[:bodypart] == bp }.sample
end.shuffle

Benchmarking these shows the select approach as the more effective on a small scale:

Queries:            0.072902   0.020728   0.093630 (  0.088008)
Select:             0.000962   0.000225   0.001187 (  0.001113)
MrYoshiji's answer: 0.000072   0.000008   0.000080 (  0.000072)

My question is whether there's an efficient way to achieve this output, and, if so, what that approach might look like. Ideally, I'd like to keep this to a single db query.

Happy to compose this using ActiveRecord or directly in SQL. Any thoughts greatly appreciated.

Answer 1

From my comment, you should be able to do (thanks PostgreSQL's DISTINCT ON):

Exercise.select('distinct on (bodypart) *')
        .order('bodypart, random()')