Search code examples
postgresqlelixirecto

Fetch random records from Ecto database

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)
Solution

  • 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 , 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