Fetch random records from Ecto database

Question

Is it possible to fetch 10 random records from database using only pure Ecto queries, and not on application side? For example, I'm don't want to get all records from the database and then take random values from them in Elixir (like this):

Subscribers
|> Repo.all
|> Enum.take_random(10)

Katleen

Answer 1

Ecto doesn't provide a generic random function for retrieving records, because the underlying databases/schemas can (and do) implement it in very different ways.

But if you know what database you're dealing with, you can use fragment/1 and limit/3 to achieve that. For postgresql, you can use them with the RANDOM() function:

import Ecto.Query

query =
  from Subscribers,
  order_by: fragment("RANDOM()"),
  limit: 10

Repo.all(query)

---

This is equivalent to calling this sql query:

SELECT * FROM subscribers ORDER BY RANDOM() LIMIT 10

Debatable

(Remember to call import Ecto.Query first) – Debatable

Answer 2

I found this question trying to figure out how to grab a single random record that has a nil parent_task property.

thought I'd post my solution incase it helps anyone.

solution 1

if someone knows how to avoid doing List.first and only use Ecto queries I'd appreciate a comment!

    query =
      from t in Subscriber,
      where: is_nil(t.parent_task),
      order_by: fragment("RANDOM()"),
      limit: 1

    Repo.all(query) |> List.first

solution 2

Repo.all(Subscriber) |> Enum.filter(fn x -> is_nil(x.parent_task) end) |> Enum.random()

Gerhardt

Instead of Repo.all(query) |> List.first(), you can use Repo.one(query) or Repo.one!(query) – Pino