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)

score 17 · Accepted Answer · edited Jan 09 '22 at 20:07

17

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

edited Jan 09 '22 at 20:07

Chris Keele

3,364
3
30
52

answered Nov 06 '18 at 05:16

Sheharyar

73,588
21
168
215

1

(Remember to call `import Ecto.Query` first) – Sheharyar Nov 06 '18 at 05:31

score 1 · Answer 2 · answered Jul 24 '20 at 07:11

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

Instead of `Repo.all(query) |> List.first()`, you can use `Repo.one(query)` or `Repo.one!(query)` — eahanson, Aug 23 '21 at 01:40

Fetch random records from Ecto database

2 Answers2

solution 1

solution 2