Complex Sql + Pagination + Sort In Cgridview

Hey, I am having a query for a list of books where I would like to display the title in the best known language of a user available in the db. The following SQL (PostgreSQL) is only to demonstrate why I will need to place LIMIT, OFFSET and ORDER BY in the middle of the query and not in the end as assumed by CSqlDataProvider or CArrayDataProvider:


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?

Hi klammeraffe,

Basically I don’t think it’s possible to apply different 'LIMIT’s, 'OFFSET’s and/or 'ORDER’s to the primary model and to the related model(s) at the same time when we use an eager loading. Because we are dealing with one big virtual table that is the result of joining the primary table and the related table(s) when we are doing eager loading.

Is it possible to apply different LIMITs to the primary table and the joined table(s) at the same time when we write plain SQL? I’m not a SQL expert, but it is not possible as far as I know. If it is impossible with plain SQL, how can we do it with eager loading of AR?

When we want the proper pagination (OFFSET and LIMIt) for the primary table, we have to construct the virtually joined table so that it will not have the redundant duplicated rows of the primary table … we have to group the result set by PK of the primary table to get the correct pagination for the primary table. And it means that we can not have the multiple row data of the the related table in the virtually joined table.

I think it’s one or the other: the proper pagination of the primary model without related models, or the whole data (without OFFSET and LIMIT) of the primary model with the proper data of the related models.

I hope you may find this wiki interesting to you:

http://www.yiiframework.com/wiki/428/drills-search-by-a-has_many-relation/

Hey Softark,

at first I thought we are talking about different things - but than I saw, I simply didn’t understand where my thinking was wrong. You are right - I can only have one - either “the proper pagination of the primary model without related models, or the whole data (without OFFSET and LIMIT) of the primary model with the proper data of the related models” since I can’t just move code inside of the query around.

Thank you for making that clear.

My last option would be to have a different $dataProvider for every sorting. I will see if that is worth the effort.

Hey guys,

I was searching because I am having a similar problem.

I have a complex query on big tables with some joins. I want to set a LIMIT to an inner query AND I want to have the LIMIT of the pagination at the end too. I can set the query manually and it works. First the result of the inner query is calculated and limited and then the joins to other tables are made that enlarges the result. In the end I want to set a LIMIT on the overall result.

Is it possible to manually set the limit again in my inner query. Is it possible to get the parameter of the pagination setting the limit and set it in the middle of the query?

How did you finally solve your problem?