in reply to Re^2: Get top N rows in each group with DBIx::Class
in thread Get top N rows in each group with DBIx::Class

I think I would have to create a view for each scenario: i.e. one view for the case where the user wants the RS to be limited to a certain Artist, another when the user wants the RS limited to a certain Genre, etc.

Being a view is not generated, there is no problem in writing a complex view that would have some insane query plan if run without a where clause, and then querying for each small case needed, resulting in a very quick runtime.

If the groups are small (artist, genre, etc), just create one view to order them all complete with row number, a column to identify which group it is, and perhaps what ordering is being used. Then, querying it for a specific group, ordering, or limit is simple.

select Lucutus from Borg where grouping in ('artist', 'genre') and ordering = 'popularity' and row_number <= 5;

Replies are listed 'Best First'.
Re^4: Get top N rows in each group with DBIx::Class
by 1nickt (Abbot) on Nov 27, 2017 at 13:26 UTC

    Hm, that sounds like a good idea, to assign intra-group row numbers to all records first, then constrain in all other ways, then constrain to N per group, knowing that the row numbers may not be sequential but will be ordered. Thanks, I'll try it!

    The way forward always starts with a minimal test.

      Let us know if it works!

      BTW, this is a tactic easily used with CTEs, where each CTE is like another step. With good naming (long table names) is can be self-explanatory too.

      If you cannot use CTEs, just nest sub-tables. Same difference, though not as clear due to formatting.