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! :)
There are two approaches you could use:
ROW_NUMBER() OVER (PARTITION BY your_group)in MySQL using variables and select only the rows with row number 25 or less.
The second is quite difficult to write correctly.
There's already an accepted answer, but I think this may be useful.
GROUP_CONCAT allows you to merge multiple rows into a single row in a MySQL query. Using this, you could concatenate the authors into a list as one field.
SELECT GROUP_CONCAT(author) FROM books GROUP BY book_id;