Re^3: 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;

Comment on Re^3: Get top N rows in each group with DBIx::Class Download Code

Replies are listed 'Best First'.
Re^4: Get top N rows in each group with DBIx::Class by 1nickt (Canon) 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.	[reply]
Re^5: Get top N rows in each group with DBIx::Class by chacham (Prior) on Nov 27, 2017 at 13:31 UTC
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.	[reply]