Shuffled Records in Phoenix

I've written previously about a way to query for records in a shuffled order that works for pagination.

The TL;DR was: use a shuffled (not random) ordering, based on a prime integer parameter.

SELECT whatever
FROM table
ORDER BY large_integer_field % prime_integer_parameter -- modulo to shuffle
LIMIT 10 OFFSET 0;

Reuse the prime_integer_parameter with OFFSET 10 for subsequent page requests.

This technique would be slow with large record sets and has issues when large_integer_field values start at 1; see the previous post for further discussion.

Applying this in Phoenix

I came up with this while working on a Phoenix-backed JSON API for Mealthy.com, using ja_resource and ja_serializer, and thought I'd show how I implemented it there.

The idea is this:

• A client could request something like /api/recipes?filter[shuffle]=true
• The server chooses a shuffle value - say, 11
• It shuffles the records accordingly
• In the response, it includes pagination links with ?filter[shuffle]=11

To that end, I created a plug that picks the shuffle value and puts it into the connection as needed for filtering and paginating.

defmodule Mealthy.Api.AutoShuffleParam do

  def init(opts), do: opts

  # Exists to support API requests with `filter[shuffle]=true`;
  # picks a random "shuffle value" and updates the params with it,
  # so the request is treated as if it had been, eg, `filter[shuffle]=31`
  def call(conn = %{params: %{"filter" => %{"shuffle" => "true"}}}, _opts) do
    shuffle_val = Mealthy.Shuffle.random_shuffle_val()
    # conn.params is what ja_resource passes to filter functions
    conn = put_in(conn.params["filter"]["shuffle"], shuffle_val)
    # conn.query_params is what ja_serializer uses to build links.next
    conn = put_in(conn.query_params["filter"]["shuffle"], shuffle_val)
    conn
  end

  def call(conn, _opts) do
    conn
  end

end

The Shuffle module implements random_shuffle_val/0. It also implements shuffle_query/2, which lets us easily shuffle records of any kind.

defmodule Mealthy.Shuffle do

  # These numbers are to be used as the modulus to shuffle the order of records
  # returned, via `ORDER BY id % val, id`.
  # They are:
  # - prime, because i % 2 and i % 4 will often be the same; primes give very
  # different orderings
  # - smallish, because we don't have a ton of records. Eg, if we did
  #  `ORDER BY id % 200, id`, it wouldn't change the ordering of the first
  #  200 records at all, because any of them % 200 is itself.
  @shuffle_vals ~w[2 3 5 7 11 13 17 19 23 29 31 37]

  def random_shuffle_val do
    Enum.random(@shuffle_vals)
  end

  def shuffle_query(query, "true") do
    shuffle_query(query, random_shuffle_val())
  end

  def shuffle_query(query, val) do
    import Ecto.Query
    val = String.to_integer(val)
    from row in query,
      order_by: [
        fragment("? % ? DESC", row.id, ^val),
        desc: row.id,
      ]
  end
end

Then we can use Mealthy.Shuffle.shuffle_query(query, val) from any of our filter clauses for ja_resource.