Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

Re^3: Get top N rows in each group with DBIx::Class

by chacham (Prior)
on Nov 27, 2017 at 13:20 UTC ( #1204322=note: print w/replies, xml ) Need Help??

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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1204322]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (9)
As of 2020-12-01 09:48 GMT
Find Nodes?
    Voting Booth?

    No recent polls found