How to select the latest entry from database with Ecto/Phoenix

Question

I'm just starting with databases and queries (coming from front-end) and I would like to select the latest entry from the fields that contain a specific title, say Page1. I know I have two fields that can help me: 1) created_at - I can order it an select only the latest one; 2) id - I know that every entry generates a new id so I can get the biggest id; I want to apply it to the "standard" edit controller action:

 def edit(conn, %{"id" => id}) do
    user = Repo.get(User, id)
    changeset = User.update_changeset(user)
    render(conn, "edit.html", user: user, changeset: changeset)
  end

So I would like to know which method is better and to see an example of the query.

Answer 1

Using the id field depends on your primary key being an integer. This may be the case currently, but will it always be the case? You can be sure that inserted_at will always be a time field - so I would use that. Regardless of which you use, be sure to order it in descending order so that the most recent item is first.

title = "Page 1"
query = Ecto.Query.from(e in Entry,
  where: e.title == ^title,
  order_by: [desc: e.inserted_at],
  limit: 1)
most_recent = Repo.one(query)

It is worth noting that in Ecto - by default the insertion time is captured as inserted_at and not created_at - if you have a created_field then you can replace inserted_at in my example.

Answer 2

It's also possible to accomplish this with Ecto expressions syntax and Ecto.Query.last/2 like so:

alias App.Entry
import Ecto.Query

Entry |> where(title: "Page 1") |> last(:inserted_at) |> Repo.one

See docs for last/2 here!