How to write this Ecto query?

I’ve been struggling with this query for some time. It needs to be as fast as possible so I can’t have multiple queries.

UPDATE subscribers
SET last_pushed_at = now()
FROM (
  SELECT id
  FROM subscribers
  ORDER BY last_pushed_at ASC NULLS FIRST
  LIMIT 5
) oldest
WHERE oldest.id = subscribers.id
RETURNING subscribers.username

Here’s what I had so far… although it definitely doesnt work lol

sq = from(s in Ztz.Subscriber,
  select: {s.id},
  order_by: [:asc_nulls_first],
  limit: ^demand
)  

subscribers = Repo.update_all(
  from(oldest in subquery(sq),
    set: [last_pushed_at: DateTime.utc_now()],
    where: oldest.id == s2.id,
  )
)
1 Like

@9mm Can you:

  1. Next time quickly create minimal (i.e. copy only important parts like model with used fields in query + of course migration) repo for each your ecto issue, because it’s fastest to write response. People usually test their code before push possible solution. It’s slower when everyone would need to create such repo locally on his own. Simply git clone and people can start work on it.

  2. Describe your problem. You said that it does not work, but you did not give us any details. Since I did not created repository yet I only can guess what’s problem, because I can’t even compile your query now.

1 Like

Oh sure, I will do that in the future. Thanks

defmodule Ztz.Subscriber do
  use Ecto.Schema

  schema "subscribers" do
    field(:username, :string)
    field(:last_pushed_at, :naive_datetime)
    timestamps(inserted_at: :created_at)
  end
end

Is that what you mean? If you are referring to the migration, I don’t have this one for this particular project because I’m communicating with a DB where the migrations are handled by a separate rails app.

As far as question 2, it doesn’t work just because I actually don’t know how to get it to work (it’s not like a specific error is stopping me, I just literally don’t know how to formulate the 2 queries together such that I can access the oldest.id and subscribers.id in the FROM.

@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

Damn… ok, well is there a way I can manually call a function to escape input for that query to prevent SQL injection/

Personally I think that: $1::integer (as in example code) should be enough, but I’m not sure about this. There is no information about possible SQL Infection problems in documentation of Ecto.Adapters.SQL module. I’m 100% sure that String interpolate "… #{…} …" is not safe at all, so in this example I have used it only for table name which I fetch at compile time from schema (not from user).

I think that best knowledge about SQL security would have Ecto-Core-Team.

Here is also one helpful article I have found: Bulk update unique values with Ecto and PostgreSQL.

2 Likes

Awsome, thank you so much Eiji

How would I go about returning the raw list back into the structs?

If I remember correctly this function would always return List, because we are working on raw query. Not sure if it’s possible to easily translate it back into structs keeping in mind all ecto features. Maybe there is some API. If so you would need to find it in documentation. I did not used such function before. If there is no public API for it then you should not use private API as it could be changed in every commit and your app could be easily broken after possibly any update.

Heres how @Eiji for future reference:

{:ok, result} = SQL.query(Repo, @sql, [count])
Enum.map(result.rows, &Repo.load(MyApp.Subscriber, {result.columns, &1}))

Actually that wasnt fast enough, so I did this:

{:ok, result} = SQL.query(Repo, @sql, [count])
columns = result.columns |> Enum.map(fn (c) -> String.to_atom(c) end)
result.rows |> Enum.map(&Enum.zip(columns, &1)) |> Enum.map(&Enum.into(&1, %{}))
1 Like