This project is archived and is in readonly mode.

Calculations#count ignores order with offset

#6551

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.

Reported by koichirose · March 9th, 2011 @ 07:36 PM

Activity

Dmitry Ratnikov
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.
March 9th, 2011 @ 07:45 PM