I have a table with books a table with authors and a table relating books to authors. A book can have more than one author, so when I do my big query for this results I might get more than one row per book, if the book has more than one author. I then merge together the results in the PHP, but the thing is that if I LIMIT - OFFSET the query for pagination, I might get less than 25 (desired) unique books per page.
Can anyone think of a (or is there a built-in) way to have the LIMIT affect a grouped-by query but still get all the results? I'd rather not do one grouped-by query and then do other queries to get each author because I lose the benefit of cached results.
If not, I'll probably do a pre-pass saving the cached results and then query each author separately.
I had exactly this same problem in a different use case (theater reservation system) and after some research and testing, I've used the pre-pass approach. It's fast and clean and works very well even with a large number of rows (in my case, over 600k). Hope it helps! :)