Hi chacham, thank you for your reply.
I'm not understanding what you are trying to do
I'm trying to query a ResultSet (i.e. a table) that contains many rows for each "group":
I want to be able to pass a value for "limit_per_artist" and get as a new ResultSet, e.g. with value "2":id | artist | album -------------------- 01 | AA | aa 02 | AA | ab 03 | AA | ac 04 | BB | ba 05 | BB | bb 06 | BB | bc 07 | CC | ca 08 | DD | da 09 | DD | db --------------------
id | artist | album -------------------- 01 | AA | aa 02 | AA | ab 04 | BB | ba 05 | BB | bb 07 | CC | ca 08 | DD | da 09 | DD | db --------------------
This is needed because in the real DB there are up to hundreds of rows per "artist" and therefore the vast majority of rows are not wanted.
Why not use SQL?
The application (CPAN Testers API) is already well established as a set of DBIC classes, and the preference is to stick to that if possible. I showed a MySQL solution in my OP, and Shadowsong showed another SQL technique in his/her reply.
why not create a view, and just query that instead?
That seems to be the most promising path, although as I explained, this query is to take place on a RS after a number of other queries. So 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. (If it were me I would implement all that directly in SQL::Abstract, but as I said this project is built in DBIC.)
another way to do it, is with UNION ALL and subqueries
I have seen this technique, but I don't believe it scales to a large number of "groups", and also, it's unknown what the "groups" are and I am trying to avoid doing multiple passes over the table. This seems like it would be a good way to go, constructing the UNION query in Perl, if it was acceptable to first get a list of "groups" with select distinct name from artists or similar.
I'm coming to think that this situation is an excellent example of the Corion Doctrine ("ORMs are great until you need to do something complicated with them, then they are just an obstacle "), and that I'll have to implement something "clunky" like multiple views, or a multiple-pass solution. It doesn't seem that DBIC is going to be able handle it (and there has been resounding silence on the IRC channel where I've asked for help twice so far).
I'd be glad to find I've missed something or misunderstood something, please let me know if so. Thanks!
In reply to Re^2: Get top N rows in each group with DBIx::Class
by 1nickt
in thread Get top N rows in each group with DBIx::Class
by 1nickt
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |