Fragment, SQL over partition by not working

Hi,

I want to preload the first 3 ranks for a list of tops. The following repository contains an example: https://github.com/obsidienne/bug_ecto

I have the following bug trace:

(Ecto.QueryError) deps/ecto/lib/ecto/association.ex:501: field BugEcto.Top.top_id in where does not exist in the schema in query:

 from t in BugEcto.Top,
   join: f in fragment("SELECT *, row_number() OVER (\n  PARTITION BY top_id\n  ORDER BY position DESC\n) FROM ranks\n"),
   on: true,
   where: f.row_number <= ^2 and f.top_id == t.id,
   where: t.top_id in ^[32, 31],
   order_by: [asc: t.top_id],
   select: {t.top_id, t}

I think i’m doing something wrong or it’s an echo bug.

Any ideas ?

Thanks,

You are trying to access t.top_id where t is an BugEcto.Top schema but there is no such field according to your schema: https://github.com/obsidienne/bug_ecto/blob/master/lib/top.ex#L6-L10

Thanks for your reply.

I want to apply the elements in this : blog post to preload the ranks (using Repo.preload: https://github.com/obsidienne/bug_ecto/blob/master/test/bug_ecto_test.exs#L35)

I have tried this code

def with_ranks(per \\ 2) do
  query = """
    SELECT * FROM (SELECT *, row_number() OVER (
      PARTITION BY top_id
      ORDER BY position DESC
    ) FROM ranks) AS t WHERE t.row_number <= $1::integer;
  """
  Ecto.Adapters.SQL.query(BugEcto.Repo, query, [per])
end

I have the following error:

** (ArgumentError) invalid preload `{:ok, %Postgrex.Result{columns: ["id", "position", "name", "top_id", "row_number"], command: :select, connection_id: 11348, num_rows: 4, rows: [[124, 4, "marie", 31, 1], [123, 3, "john", 31, 2], [128, 4, "bob", 32, 1], [127, 3, "carrie", 32, 2]]}}` in `[ranks: {:ok, %Postgrex.Result{columns: ["id", "position", "name", "top_id", "row_number"], command: :select, connection_id: 11348, num_rows: 4, rows: [[124, 4, "marie", 31, 1], [123, 3, "john", 31, 2], [128, 4, "bob", 32, 1], [127, 3, "carrie", 32, 2]]}}]`. preload expects an atom, a (nested) keyword or a (nested) list of atoms

I want to preload (ecto) the first 2 Rank per Top. In SQL, we use the OVER PARTITION BY. But in Ecto…

Maybe an ecto subquery…

The preload function requires you to return a list of structs. You are
returning a tuple with a Postgrex.Result struct. You need to take the data
out accordingly.

I was searching a way to preload without iterating. Seems there is not actually in Ecto for a OVER PARTITION BY query syntax.

Thanks for the help.