WhereRow

A minimalistic Rails gem to allow easy use of SQL row value syntax.

Sometimes, the classic offset method to paginate results can be very inneficient, and not the best approach for some problems such as infinite scrolling. The seek method is a good alternative for these cases.

Consider the following example, as it appears on Use The Index Luke

Suppose we want to have an infinite scrolling functionality for a collection of sales, ordered by the date the sale took place. Ordering just by sale_date will not suffice, since many sales can occur on the same date. Hence, we need to order by both the date and the id, to have a deterministic order. In SQL, this would look like this:

CREATE INDEX sl_dtid ON sales (sale_date, sale_id)

SELECT *
  FROM sales
WHERE (sale_date, sale_id) < (?, ?)
ORDER BY sale_date DESC, sale_id DESC
FETCH FIRST 10 ROWS ONLY

The Row Value syntax is not supported in Rails directly. Furthermore, some databases still don't support this syntax as well, or maybe there is partial support (for example, the index is not properly utilized).

Thankfully, the same results can be achieved with plain-old logical expressions and comparisons. The equivalent query would look like this:

SELECT *
  FROM sales
WHERE sale_date <= ?
AND NOT (sale_date = ? AND sale_id >= ?)
ORDER BY sale_date DESC, sale_id DESC
FETCH FIRST 10 ROWS ONLY

This is something that can be directly expressed in Rails. One possible way is the following:

Sales.
  where(sale_date: (..date_offset)).
  where.not(sale_date: date_offset, sale_id: (sale_id_offset..)).
  order(sale_date: :desc, sale_id: :desc).
  limit(10)

However, the intent of this query is not clear at all when reading through this piece of code. Furthermore, if for any reason we need more than two columns (maybe by the sale's client_id), this will blow up pretty quickly. This gem allows us to generate this query/relation with a more explicit syntax.

Sales.
  where_row(:sale_date, :sale_id).lt(date_offset, sale_id_offset).
  order(sale_date: :desc, sale_id: :desc).
  limit(10)

Add this line to your application's Gemfile:

gem 'where_row'

And then execute:

$ bundle

Or install it yourself as:

$ gem install where_row

A single where_row method is made available for all relations.

date = Date.new(2021, 4, 5)

Sales.where_row(:sale_date, :sale_id).eq(date, 42)
Sales.where_row(:sale_date, :sale_id).in([date, 42], [date + 1.day, 43])
Sales.where_row(:sale_date, :sale_id).lt(date, 42)
Sales.where_row(:sale_date, :sale_id).gt(date, 42)
Sales.where_row(:sale_date, :sale_id).gte(date, 42)
Sales.where_row(:sale_date, :sale_id).lte(date, 42)

There is also a not method for negated queries.

Sales.where_row(:sale_date, :sale_id).not.eq(date, 42)

The result is also a relation, so it can be chained with regular Rails query methods.

Bug reports and pull requests are welcome on GitHub at https://github.com/odydoum/where_row.

The gem is available as open source under the terms of the MIT License.