[ECTO] select additional values without virtual fields

I’m trying to get last log for each object. In SQL it’s pretty simple. I need to select logs as well as ranks then do a subquery and filter only rank 1. Here’s an example query.

SELECT rank_filter.* FROM (
    SELECT items.*, 
        rank() OVER (
            PARTITION BY color
            ORDER BY created_at DESC
        )
   FROM items
   WHERE items.cost < 50
) rank_filter WHERE RANK = 1

But I can’t write items.* in a reasonable way in ecto. This fails because Item doesn’t have rank field. Which would be weird to have because this is the only place in the whole codebase where I need it. So I don’t want to go down virtual field path for this reason. It would be also confusing for other devs to see rank there.
|> select([i], %{i | rank: rank() |> over(partition_by: i.color, order_by: i.inserted_at)})

I could convert i into struct, but that would need to define all fields I want to select. In case a field is added or removed this code breaks. I’ve also tried select_merge/3 with the same results. key :rank not found in: %Item{....}. It would be real neat if I could convert Item into map without having to define all the fields I want to have.

Untested but I believe you can do something like

fields = MySchema.__schema__(:fields)

from(a in MySchema,
  select: map(a, ^fields),
  select_merge: …
)

Edit: to clarify as well, this reflection API is public and documented here: Ecto.Schema — Ecto v3.9.1

2 Likes

Tested and it works!

1 Like