How to write this Ecto query?

Questions / Help
4

@9mm Unfortunately as it’s discussed in this topic there is no way to do update … from … syntax using ecto's Query API. However you can write such SQL on your own and execute it like this one:

defmodule Ztz do
  alias Ecto.Adapters.SQL
  alias Ztz.{Repo, Subscriber}

  @table_name Subscriber.__schema__(:source)
  @sql """
  update #{@table_name} subs
  set last_pushed_at = now()
  from (
    select id
    from #{@table_name}
    order by last_pushed_at asc nulls first
    limit $1::integer
  ) oldest
  where oldest.id = subs.id
  returning subs.username
  """

  def example(demand \\ 5), do: SQL.query(Repo, @sql, [demand])
end
2 Likes