This project is archived and is in readonly mode.
Calculations#count ignores order with offset
Reported by koichirose | March 9th, 2011 @ 07:36 PM
I'm executing a query with limit, offset and order.
Code:
@restaurants = Restaurant.where(:city_id => params[:city_id]).geo_scope(:bounds => [@sw, @ne]).order("name asc").limit(12).offset(params[:start])
And here's the log:
SELECT `restaurants`.* FROM `restaurants` WHERE `restaurants`.`is_active` = 1 AND `restaurants`.`city_id` = 1 AND (restaurants.lat>45.42787 AND restaurants.lat<45.500107 AND restaurants.lon>9.022518 AND restaurants.lon<9.355542) ORDER BY name asc LIMIT 12 OFFSET 0
Everything is ok for now.
If params[:start] (i.e. the offset) is > 0 then only the
following query is executed:
SELECT COUNT(*) FROM `restaurants` WHERE `restaurants`.`is_active` = 1 AND `restaurants`.`city_id` = 1 AND (restaurants.lat>45.42787 AND restaurants.lat<45.500107 AND restaurants.lon>9.022518 AND restaurants.lon<9.355542) LIMIT 12 OFFSET 12
This is returning an empty set, which is wrong.
If I add '.all' to the same query (overriding, I believe, the
Rails optimization system), then it works:
@restaurants = Restaurant.where(:city_id => params[:city_id]).geo_scope(:bounds => [@sw, @ne]).order("name asc").limit(12).offset(params[:start]).all
This is executed instead:
SELECT `restaurants`.* FROM `restaurants` WHERE `restaurants`.`is_active` = 1 AND `restaurants`.`city_id` = 1 AND (restaurants.lat>45.42787 AND restaurants.lat<45.500107 AND restaurants.lon>9.022518 AND restaurants.lon<9.355542) ORDER BY name asc LIMIT 12 OFFSET 12
And it returns the expected set.
I'm sorry if this is not technical enough, but this is my very
first experience with Rails.
I'll be happy to provide more details if needed.
Comments and changes to this ticket
-
Dmitry Ratnikov March 9th, 2011 @ 07:45 PM
Here's a piece of code similar to what originally caused the issue:
@posts = Post.offset(10).limit(10)
<% unless @posts.empty? -%>
<% @posts.each do |post| -%> <%= post.title %> <% end -%><% else -%>
No posts!<% end -%>
Since @posts.empty? issued count without order, it returned as if there were no objects, preventing the posts to be displayed.
After digging around, seems like the issue is with https://github.com/rails/rails/blob/master/activerecord/lib/active_... which was there intentional.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
<h2 style="font-size: 14px">Tickets have moved to Github</h2>
The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>
koichirose