SELECT s.title, s.bookid FROM ( with mybooks as ( SELECT bookid, dateread FROM books ORDER BY dateread DESC LIMIT :limit OFFSET :offset) SELECT title, bookid FROM mybooks s INNER JOIN ( SELECT bookid, title, row_number() OVER ( PARTITION BY bookid ORDER BY CASE WHEN languageid = 'deu' THEN 1 WHEN an.languageid = 'eng' THEN 2 ELSE 3 END ) AS rn FROM mybooks s INNER JOIN book_titles USING (bookid) ) tit on (s.bookid = tit.bookid AND tit.rn = 1) ORDER BY s.dateread DESC ) s;
What it does is - taking the last :limit books some user has read starting from :offset. Only now of those books: look for the german, english or any other title in this order and give them numbers. What ever is met first gets rn = 1 and will be in the result set. Like that I only need to find out the titles of the i.e. 20 books requested by pagination.
If I would need to place the OFFSET, LIMIT and ORDER BY at the end of the query as required by CSqlDataProvider I would need to find out the titles of all the books in the list first and then only use the first 20 of them. And in the real world also the right cover and authorname. In case the complete book list is long this can take by far too long.
So is there any way I can have a query like above and still use the sorting and pagination features of CGridView?