In Active Record, why can Restaurant.order(rating: :desc).limit(10).offset(10) show the same restaurant on two pages, and how do you fix it?
answer
- ties in the sort key
- the database may break ties differently
- add a unique tiebreaker column
- limit is replaced, not stacked
- offset without order is arbitrary
basics
~10 sMany restaurants share a rating, and SQL leaves the order of tied rows undefined, so each LIMIT/OFFSET query may break ties differently. Add a unique tiebreaker: order(rating: :desc, id: :desc).
solid answer
~40 s`limit(10)` adds `LIMIT 10` and `offset(10)` adds `OFFSET 10`, but they only slice whatever order the database produced. If forty restaurants share a 4.5 rating, `ORDER BY rating DESC` does not say which of them comes first, and two separate queries can resolve the tie differently, so one restaurant shows on pages 1 and 2 while another never appears. The fix is a **total order**: append a unique column, `order(rating: :desc, id: :desc)`. Related mechanics: a second `limit` replaces the first rather than stacking (`limit(10).limit(20)` is `LIMIT 20`), `limit` converts its argument with `Integer()` so a non-numeric string raises `ArgumentError`, and `offset` without any `order` is arbitrary. Deep offsets also get slower because the database still walks the skipped rows.
code
ruby · 15 linesPER_PAGE = 10
def top_restaurants(page)
page = [page.to_i, 1].max
Restaurant
.order(rating: :desc, id: :desc) # unique tiebreaker
.limit(PER_PAGE)
.offset((page - 1) * PER_PAGE)
end
top_restaurants(2).to_sql
# ... ORDER BY rating DESC, id DESC LIMIT 10 OFFSET 10
Restaurant.limit(10).limit(20).to_sql
# ... LIMIT 20go deeper
Know that limit and offset slice an ordered result, and always pair them with an order.
Explain why ties make pages overlap, how a unique tiebreaker fixes it, and that a repeated limit replaces the earlier one.
Diagnose duplicate or missing rows across pages from the ORDER BY, add a matching composite index, and clamp page sizes taken from params.
Decide where offset paging is acceptable and where lists need keyset paging, based on depth, write rate and how much drift users will tolerate.
## What limit and offset add `limit(n)` and `offset(n)` are query methods that add `LIMIT` and `OFFSET` clauses: ```ruby Restaurant.order(rating: :desc).limit(10) # page 1 Restaurant.order(rating: :desc).limit(10).offset(10) # page 2 ``` They are simple, but they inherit every weakness of the order they slice. Hand-rolled pages like these are common in admin screens and APIs even when a pagination gem drives the public pages. ## Why ties break paging SQL only guarantees order for the columns in `ORDER BY`. Rows that tie on all of them can come back in **any** order, and that order is allowed to differ between two executions, for example because the query plan or the table's physical layout changed in between. With a restaurant guide, ties are the norm: ratings cluster at 4.0, 4.5 and 5.0. So: 1. Page 1 runs `ORDER BY rating DESC LIMIT 10` and returns ten of the forty 4.5-rated places. 2. Page 2 runs `ORDER BY rating DESC LIMIT 10 OFFSET 10` and resolves the same tie differently. 3. A restaurant appears on both pages, and another appears on neither. The fix is to make the order **total** by adding a unique column as the last key: ```ruby Restaurant.order(rating: :desc, id: :desc).limit(10).offset(10) # ORDER BY rating DESC, id DESC LIMIT 10 OFFSET 10 ``` An index on `(rating, id)` lets the database read rows already in that order. ## Mechanics of the two methods | Behaviour | Detail | |---|---| | repeated `limit` | the last call wins: `limit(10).limit(20)` is `LIMIT 20` | | argument conversion | `limit` runs `Integer(value)`, so `"10"` works and `"ten"` raises `ArgumentError` | | `limit(nil)` | removes the limit | | `offset` without `order` | allowed, but the slice is arbitrary; the Rails docs say it should be used with `order` | | `first` on a limited relation | respects the limit and offset | | `last` on a limited relation | loads the slice and takes its final element in Ruby | ## How other calls treat an existing limit - `first(n)` on a limited relation takes the smaller of the two limits and respects the offset. - `last` on a limited or offset relation loads that slice and takes the final element in Ruby. - `pluck` and `ids` keep the limit and offset, so `order(rating: :desc).limit(10).pluck(:name)` returns ten names. - `exists?` on `limit(0)` returns `false` without a query. - `sole` and `take` impose their own small limits (`LIMIT 2`, `LIMIT 1`) for their own check. ## Offsets get slower with depth `OFFSET 10000` does not jump to row 10,001: the database produces and discards the first 10,000 rows of the ordered result. Shallow pages are cheap, deep ones are not. For long lists, keyset pagination ("rows after the last rating and id I saw") avoids that cost; the trade-offs between the two styles are their own topic. ## Values coming from params - Clamp page sizes: `per = params[:per].to_i.clamp(1, 50)`, so a client cannot ask for `limit(100000)`. - Compute offsets from a clamped page number, never from a raw parameter. - Keep the order explicit in the same method that applies `limit`, so a scope's order cannot be swapped out under it. ## Checklist - Every `limit`/`offset` query has an `order`. - That order ends in a unique column. - Page size is clamped. - Deep pagination is either rare or moved to a keyset approach.
- Why is order(rating: :desc, created_at: :desc) not always enough as a tiebreaker?Two rows can share a timestamp, especially rows inserted by a seed, an import or one transaction, so the order is still not total. End with a column that is unique by constraint, normally the primary key: `order(rating: :desc, created_at: :desc, id: :desc)`.
- What does Restaurant.limit(params[:per]) do when per is "abc"?`limit` calls `Integer(value)` on non-nil values, so `"abc"` raises `ArgumentError` before any SQL is built. Convert and clamp the parameter yourself, for example `params[:per].to_i.clamp(1, 50)`, so a bad value becomes a sane page size instead of a 500.
Sorting by rating alone is like lining people up by height for two photos taken a minute apart: everyone of the same height may shuffle between shots, so someone appears in both photos and someone in neither. Adding the id is asking equal-height people to stand in name order.
- ORDER BY rating DESC returns tied rows in insertion order
- limit(10).limit(20) returns 10 rows because the smaller limit wins
- OFFSET 10000 lets the database jump straight to row 10001
- Duplicates across pages mean the table has duplicate rows
- offset without order still returns rows in primary-key order