An Effective Way to Choose One from Each Category - Rails

Question

I am developing a simple application to return a random selection exercises, one for each bodypart.

bodypartis an indexed column enumin the model Exercise. DB - PostgreSQL.

Below is the result that I want, but feel terribly ineffective (I hit db once on each 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 one Exercisefor everyone bodypartand mixes the order at the end.

I could also store all the exercises in my memory and choose from them; however, I think it will be terribly scalable (there are currently only about a dozen seed records).

@exercises = Exercise.all

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

Benchmarking shows that the approach selectis 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 answer: 0.000072   0.000008   0.000080 (  0.000072)

, , , . db-.

ActiveRecord SQL. .

Answer 1

( PostgreSQL DISTINCT ON):

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

Answer 2

Postgres 'DISTINCT ON , , , - . bodypart ( - ) .

:

SELECT e.*
FROM   unnest(enum_range(null::bodypart)) b(bodypart)
CROSS  JOIN LATERAL (
   SELECT *
   FROM   exercises
   WHERE  bodypart = b.bodypart
   -- ORDER BY ??? -- for a deterministic pick
   LIMIT  1        -- arbitrary pick!
   ) e;

, bodypart - , .

enum_range , ( ):

LATERAL , , . ( "2a. LATERAL join" ):

• GROUP BY

bodypart exercises(bodypart) . , , " " ORDER BY . :

• pg_type ?
• GROUP BY?