Will_paginate and geokit misbehavior (double query)

Question

Hi all,

I'm using will_paginate to paginate my geokit search results. The code works, however, when looking at the log it does double the geokit query when using the following will_paginate call:

@posts = Post.paginate :page => params[:page], :per_page => 1, 
                        :origin => @search, :within => @miles, :include => :user

This is the original non-paginated call which works as expected (a single query):

@posts = Post.find(:all, :origin => @search, :within => @miles, :include => :user)

The following is the log output when using the first will_paginate call:

Processing PostsController#search (for 127.0.0.1 at 2010-06-03 22:10:29) [POST]
  Parameters: {"commit"=>"Search", "action"=>"search", "authenticity_token"=>"K9Btfu6p7pz2mt+lWH0Fx0O7qj+0QY21JpfgyWT738I=", "controller"=>"posts", "location"=>"new york"}
Google geocoding. Address: new york. Result: <?xml version="1.0" encoding="UTF-8" ?>
<kml xmlns="http://earth.google.com/kml/2.0"&gt;&lt;Response&gt;
  <name>new york</name>
  <Status>
    <code>200</code>
    <request>geocode</request>
  </Status>
  <Placemark id="p1">
    <address>New York, NY, USA</address>
    <AddressDetails Accuracy="4" xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"><Country><CountryNameCode>US</CountryNameCode><CountryName>USA</CountryName><AdministrativeArea><AdministrativeAreaName>NY</AdministrativeAreaName><SubAdministrativeArea><SubAdministrativeAreaName>New York</SubAdministrativeAreaName><Locality><LocalityName>New York</LocalityName></Locality></SubAdministrativeArea></AdministrativeArea></Country></AddressDetails>
    <ExtendedData>
      <LatLonBox north="40.8494506" south="40.5788125" east="-73.7498541" west="-74.2620917" />
    </ExtendedData>
    <Point><coordinates>-74.0059729,40.7142691,0</coordinates></Point>
  </Placemark>
</Response></kml>
  Post Load (0.7ms)   SELECT *, 
 (ACOS(least(1,COS(0.710598048337988)*COS(-1.2916478932467)*COS(RADIANS(posts.lat))*COS(RADIANS(posts.lng))+
 COS(0.710598048337988)*SIN(-1.2916478932467)*COS(RADIANS(posts.lat))*SIN(RADIANS(posts.lng))+
 SIN(0.710598048337988)*SIN(RADIANS(posts.lat))))*3963.19)
 AS distance FROM `posts` WHERE (((posts.lat>40.352844467866 AND posts.lat<41.075693732134 AND posts.lng>-74.4827993840952 AND posts.lng<-73.5291464159048)) AND (
 (ACOS(least(1,COS(0.710598048337988)*COS(-1.2916478932467)*COS(RADIANS(posts.lat))*COS(RADIANS(posts.lng))+
 COS(0.710598048337988)*SIN(-1.2916478932467)*COS(RADIANS(posts.lat))*SIN(RADIANS(posts.lng))+
 SIN(0.710598048337988)*SIN(RADIANS(posts.lat))))*3963.19)
 <= 25)) LIMIT 0, 1
  Post Columns (2.4ms)   SHOW FIELDS FROM `posts`
  User Columns (2.2ms)   SHOW FIELDS FROM `users`
  User Load (0.4ms)   SELECT * FROM `users` WHERE (`users`.`id` = 1) 
Google geocoding. Address: new york. Result: <?xml version="1.0" encoding="UTF-8" ?>
<kml xmlns="http://earth.google.com/kml/2.0"&gt;&lt;Response&gt;
  <name>new york</name>
  <Status>
    <code>200</code>
    <request>geocode</request>
  </Status>
  <Placemark id="p1">
    <address>New York, NY, USA</address>
    <AddressDetails Accuracy="4" xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"><Country><CountryNameCode>US</CountryNameCode><CountryName>USA</CountryName><AdministrativeArea><AdministrativeAreaName>NY</AdministrativeAreaName><SubAdministrativeArea><SubAdministrativeAreaName>New York</SubAdministrativeAreaName><Locality><LocalityName>New York</LocalityName></Locality></SubAdministrativeArea></AdministrativeArea></Country></AddressDetails>
    <ExtendedData>
      <LatLonBox north="40.8494506" south="40.5788125" east="-73.7498541" west="-74.2620917" />
    </ExtendedData>
    <Point><coordinates>-74.0059729,40.7142691,0</coordinates></Point>
  </Placemark>
</Response></kml>
  SQL (0.4ms)   SELECT count(*) AS count_all FROM `posts` WHERE (((posts.lat>40.352844467866 AND posts.lat<41.075693732134 AND posts.lng>-74.4827993840952 AND posts.lng<-73.5291464159048)) AND (
 (ACOS(least(1,COS(0.710598048337988)*COS(-1.2916478932467)*COS(RADIANS(posts.lat))*COS(RADIANS(posts.lng))+
 COS(0.710598048337988)*SIN(-1.2916478932467)*COS(RADIANS(posts.lat))*SIN(RADIANS(posts.lng))+
 SIN(0.710598048337988)*SIN(RADIANS(posts.lat))))*3963.19)
 <= 25)) 
Rendering template within layouts/application

As you can see the KML/XML and SQL queries are doubled. Any idea what's going on and how I can fix it? Thanks!

-Tony

Answer 1

Actually this is standard behaviour. will_paginate first counts the number of records and then retrieves 20 rows, depending on the visible page and number of rows on the page (so the queries are not completely identical).

The count is needed to display the number of pages.

But your logging does not completely make sense to me, as the will_paginate query would limit the number to 20 (or whatever page-size), also the shown queries are not entirely identical: i see different conditions for instance.