CODEWITHSUNDEEP
ruby-on-railsdatabaseactiverecordlimitruby-on-rails-4
lala
lala

Reputation: 2082

Limit in db query returns wrong amount for certain values

If I do this:

@open_shops = Shop.includes(:opening_times).where("opening_times.day =?", today)
              .order("opening_times.opens+opening_times.open_for")

@open_shops.size returns 10

If I do this:

@open_shops = Shop.includes(:opening_times).where("opening_times.day =?", today)
              .order("opening_times.opens+opening_times.open_for")
              .limit(2)

@open_shops.size returns 2, as expected. .limit(1) also works as expected.

But if I do this:

@open_shops = Shop.includes(:opening_times).where("opening_times.day =?", today)
              .order("opening_times.opens+opening_times.open_for")
              .limit(3)

@open_shops.size returns 2! As you can see above, 10 records are returned when there is no limit, but only 2 are returned with a limit of 3. With any limit from 3-10, it returns one less than it should. My mind is boggling.

Removing the where statement or the order statement makes limit work as expected.

EDIT: Perhaps worth noting that it isn't the same record missing each time. It's always the last one on the list that is missing. So limit(3) returns exactly the results that limit(2) should return for example.

These are my models:

class Shop
  has_many :opening_times
end

class OpeningTime
  belongs_to :shop
end

Upvotes: 2

Views: 638

Answers (3)

trebeil
trebeil

Reputation: 31

I had a similar issue here.

Turned out the problem was in my development database data. I was also using includes in my query and one of the entries had duplicated data on the included table, and it shouldn't have.

So, in your example, it would be like if you had two entries in opening_times for one specific Shop. When this specific Shop is not included in the results, the query returns the right quantity of results. If you set limit to a higher number, it starts including this Shop in the results and, as it is duplicated, it is counted but shown only once.

So the tip is: check if you have any duplicate entries in the table which is being included using includes.

After I found and deleted this specific duplicate entry in my database table, everything worked as it should.

Upvotes: 0

deivid
deivid

Reputation: 5258

Don't know whether this might help or not at this point, or whether this was your actual problem but one case where limit(n) returns less than n records even if the expression being limited has n or more records is when you have a counter cache incorrectly set.

For example,

class ParentThing
  has_many :children_things
end

class ChildrenThing
  belongs_to :parent_thing
end

In this case, with parent_things table having a children_thing_count column,

ParentThing.find(1).children_things.size

will return whatever (correct or incorrect) it is in the children_thing_count column whereas

ParentThing.find(1).children_things.limit(n).size

will always return the correct count expression over the table with the counter.

So, be very careful with counter cache's!!

Upvotes: 1

lala
lala

Reputation: 2082

Well. I had one shop that is closed all day on Saturday (the day I was querying for). I began to wonder if this record was acting as a "ghost" getting included in the query (even though it shouldn't) without actually returning. This would explain why the last record of each query was missing, because the ghost record was knocking it off the list.

So I tried making a dummy opening time for this shop. I had it open at 00:01 and stay open for one minute. Suddenly, everything worked! If that isn't already weird enough, I then deleted this dummy record, and it still worked.

I'm not accepting my own answer out of hope that someone can explain what on earth happened. At this point, demon possession and witchcraft appear like reasonable explanations.

Upvotes: 0

Related Questions