Convert your raw sql to Ecto query

TLDR;

We're using with_cte, combined with matching the Schema when using select:

"img"
|> with_cte("img", as: fragment("select * from images TABLESAMPLE system_rows(1)"))
|> from(as: ^:img)
|> select([img: i],
  %Images{
    title: i.title,
    description: i.description,
    field :slug,
    field :tags,
  }
)
|> limit(1)
|> Repo.all

Non-TLDR

Before we get to the point, allow me to start at this part. I was contented with the randomized paginated results (using RANDOM()) that looked like this:

def paginate_product(status_name, params \\ [page: 1, page_size: 10]) do
  Product
  |> where(status_name: ^status_name)
  |> order_by(fragment("RANDOM()"))
  |> preload(:user)
  |> Repo.paginate(params)
end

TABLESAMPLE clause

Actually what I do need is:

• It (query) should be fast even on large records.
• Not required to be purely random.
• Easily pipe it in Repo.paginate (aka scrivener_ecto), as it needed an Ecto query not a list of schemas.

I'm not sure if my current implementation is enough, until I read this article:

The system_rows version of the TABLESAMPLE function will pick a random disk block in the table, and then fetch rows sequentially from there. Picking a random block can be done by just looking at the size of the table so this is very very fast. Here's the sql for the alternative version of RANDOM():

# randomly picks a starting point, then
# fetches 10 rows in order.
select * from images TABLESAMPLE system_rows(10);

Attempt #1: Repo.query!/1

I've stumbled on this answer which is great, given that it returns a list of structs. But scrivener_ecto's paginate function accepts an Ecto query. But what's an Ecto query?

something like:

iex()> Images |> limit(1)
#Ecto.Query<from i0 in Nappy.Catalog.Images, limit: 1>

But using Repo.query!/1 returns %Postgrex.Result{...} struct :(

Attempt #2: with_cte/3 function and forming the struct

Using with_cte/3, I can finally use the raw sql and expecting a query, happily passing it to paginate:

"img"
|> with_cte("img", as: fragment("select * from images TABLESAMPLE system_rows(1)"))
|> from(as: ^:img)
|> select([img: i],
  %Images{
    title: i.title,
    description: i.description,
    field :slug,
    field :tags,
  }
)
|> limit(1)
|> Repo.all

Shortcomings

Here's the raw sql source (at the very bottom of the blogpost) on using with clause. But there's a problem which I'm still solving: How can I preload and use where clause for this? I'll write another post for this once I find it.