I wrote about my troubles with pagination in RNS. The root cause was need for pagination for AJAX requests. Standard Rails pagination works well for HTML links, but page with found routes in RNS is generated via AJAX, so I had to paginate with custom output. Well, for this was pagination_links_each helper created.
But first You need to create Paginator from ActionController::Pagination module. And here my story begins, since I did not carefully read documentation, but I’ve jumped into coding.
In RNS case I have two basic models Route and Point. Route has_may :points and Point belongs_to :route. My idea for searching routes in current map was to find any points on current map and then GROUP BY route_id, which results in one point from route on current map (well I realized when I wrote, that this approach does not show routes which may go through current map, but have no point on it – distance between points is bigger than map – I need to think how to solve this issue). Selected points are drawn on map as an anchor for route.
But paginate does not support :group option. And now I was bitten by not read documentation. I start to write patch for Rails to support :group option in paginate call instead of generate paginator from custom collection as in documentation. Patch worked on my test data, but when I applied it to RNS code base, strange things start to happen. Having something like 200 points and 30 routes in test fixtures, I’ve got 22 pagination pages (with default 10 entries per page) and walking through them result in exceptions (nil.[]). First I tried to debug whole thing, but after few hours I wrote custom paginator – ugly and hairy, but it have worked. I applied it to RNS and went to get some sleep.
Next day I started with fresh mind. Looked at documentation, damn it! and used pagination for custome collections. No more ugly code in view, things become better, but I still wanted to know what happened with patch gone wild.
I examined log/development.log for SQL statements and I’ve noticed what was the cause. Well… I explain it with Rails console:
>> Point.count => 216 >> Point.count(:group => :route_id) => [[41, 8], [44, 4], [45, 43], [46, 9], [49, 30], [50, 10], [51, 5], [52, 9], [53, 9], [54, 15], [55, 4], [56, 22], [57, 4], [58, 4], [59, 5], [60, 5], [61, 5], [62, 8], [63, 2], [64, 3], [65, 4], [68, 2], [69, 2], [71, 2], [72, 2]] >> Point.count(:group => :route_id).size => 25 >> Route.count => 29
(4 routes does not have any points associated)
When using :group in count call, SQL returns count of each grouped collection, instead of number of collections. So when Rails executes count_collection_for_pagination in vendor/rails/actionpack/lib/action_controller/pagination.rb, line 174:
173 def count_collection_for_pagination(model, options) 174 model.count(:conditions => options[:conditions], 175 :joins => options[:join] || options[:joins], 176 :include => options[:include], 177 :group => options[:group], 178 :select => options[:count]) 179 end
Result with set :group is coherent with SQL:
ysql> SELECT count(*) FROM points ; +----------+ | count(*) | +----------+ | 216 | +----------+ 1 row in set (0.01 sec) mysql> SELECT count(*) FROM points GROUP BY route_id; +----------+ | count(*) | +----------+ | 8 | | 4 | | 43 | | 9 | | 30 | | 10 | | 5 | | 9 | | 9 | | 15 | | 4 | | 22 | | 4 | | 4 | | 5 | | 5 | | 5 | | 8 | | 2 | | 3 | | 4 | | 2 | | 2 | | 2 | | 2 | +----------+ 25 rows in set (0.00 sec)
Almost the same result ;-)) Almost makes difference.