Feed Gridview with pre-searched data

Not the best subject line but I’m not sure how else to word it.

I want to feed a gridview with a custom query in which I have already limited the dataset based on the entries in the search form.

Gridview works well without any pre-Query for limited sized datasets but when the sets get REALLY large I want to be able to use the grid for its flexible table display (paginate, sort) rather than a filter.

What I have is a form with several fields, which when posted runs a SQL query that joins 5 tables to produce a result. I tried to run this query via SQLDataProvider but it tells me one of the tables does not exist (even though DAO works). I assume this issue has something to do with MS-SQL so I didn’t follow it up further.

My second attempt was to create an ArrayDataProvider from the SQL query using DAO queryAll(). So, I POST my form, run the query, and feed the grid using the ArrayDataProvider. The problem here is that if I want to access the 2nd page of results or sort the grid the grid disappears when I click on it.

My third attempt (aka ‘Screw-it!’) was to run my query from the POSTed data and just populate an HTML table. Looks nice, but I need to implement pagination, filtering, etc now which would probably involve some ugly code on the client side.

I really want to make attempt #2 work - I could just follow the ‘advanced’ search that Gii generates in the admin view but I do not necessarily want to SHOW all the fields in the grid that I am querying.