Selecting a row by latests inserted_at (noob alert)

Questions / Help
1

Hello There! :slight_smile:
I have researched Ecto.Query — Ecto v3.7.1 but could not find what I was looking for. Let’s say we have this example psql table:

 id  |   user_id | room_id |        inserted_at     |     updated_at     
-----+-----------+---------+--------+---------------------+---------------------+--------
  64 |         4 |       2 |    2021-12-05 22:42:31 | 2022-01-25 08:28:15 |       
  61 |         3 |       2 |    2021-12-06 22:36:11 | 2022-01-25 08:28:46 |       
 112 |         2 |       2 |    2022-01-25 08:28:08 | 2022-01-26 12:08:19 |

I want to get the latest inserted_at row, in this case - id.112. I actually don’t know how this “function” is called, noob alert :slight_smile:

query = from(e in Example, where: e.inserted_at == ???)
Repo.all(query)

For me a where expression should be used, maybe smth kind of where: e.inserted_at > all other rows. The answer is Ecto.Query — Ecto v3.7.1 for sure, but frankly I don’t know what to search for.

Best Regards :slight_smile:

1 Like
2

What You can do is sort by inserted_at, :desc, limit: 1and take one :slight_smile:

For example…

import Ecto.Query
query = from q in Accounts.User, order_by: [{:desc, :inserted_at}], limit: 1
user = Repo.one query

You can also use max

https://hexdocs.pm/ecto/Ecto.Query.API.html#max/1

5 Likes
3

order_by is the simplest way if you’re only looking for one record; if you want something more complicated like "give me the most recent row from this table for each user_id" you’ll need to look into window functions.

FWIW, the Ecto docs focus mostly on “how to make SQL shaped like X” with Ecto but assume that you know how to translate a requirement like "the latest row by inserted_at" into “I need SQL shaped like X”. If you’re just getting started with both SQL and Ecto, a plain SQL tutorial / reference will help you a lot.

3 Likes
4

actually yes, that will do the job. Idk, but i was looking for more complicated solution…

Thank you for the answer, I not getting started with sql just now, but can’t say i am an advanced one, so will read some tutorials on plan SQL.

5

If you are going to do this kind of thing you should also create an index on inserted_at. Otherwise the database will have to scan the entire table and sort it.

3 Likes