How to get a row by id with row number in Ecto?

Questions / Help
1

I’m trying to get a row with its row number. For example, I have the following table:

+------------+-----+------+
| row_number | id  | name |
+------------+-----+------+
| 1          | 100 | foo  |
+------------+-----+------+
| 2          | 101 | bar  |
+------------+-----+------+

I want to get a row with id 101: %{row_number: 2, id: 101, name: "bar"} or `%{row_number: 2, entry: %{id: 101, name: “bar”}

I have SQL that does what I need but I don’t know how to write this in Ecto:

WITH shp AS (
 SELECT *, ROW_NUMBER() OVER (ORDER BY s.id) FROM public.ships as s
) SELECT * from shp WHERE shp.id = 2

I was trying to do this:

ships_indices_query =
  from s in Ship, select: %{ship: s, row_number: row_number() |> over(order_by: s.id)}

query = 
  from si in ships_indices_query, where: si.ships.id == ^id, select: si

Repo.one(query)

But I get an error:

** (Ecto.Query.CompileError) `si.ships().id()` is not a valid query expression. If you want to invoke si.ships().id/0 in a query, make sure that the module si.ships() is required and that id/0 is a macro

Can someone please help me?

2

Is row number a column in the database? Or do you try to calculate it artificially “live” when doing the query?

1 Like
3

No, it’s not in the database. Sorry, forget to attachment SQL that does it. I updated post.

4

Add virtual field to Ship schema

field(:number, :integer, virtual: true)

And use the following query:

ships_indices_query =
  from s in Ship, select: %{s | number: row_number() |> over(order_by: s.id)}

query = 
  from si in subquery(ships_indices_query), where: si.id == ^id
6 Likes
5

Thanks!