Filtering query further with ecto's windowAPI

Questions / Help
,
1

I’ve been using Ecto for a small project and everything has been falling into place until I need to use window API.

The issue is that I want to filter a particular item out of windows result.

the query is like this

query = from u in User, as: :user, windows: [
  w: [partition_by: u.category, order_by: [desc: u.activity_count]]
], select: {rank() |> over(:w), t.name, t.activity_count}

I only want to get a particular user with an id of 5 and his/her current rank based on activity count.

This is what I’m currently doing.

id = 5
from user in query, where: user.id == ^id

I understand that ecto is merging a query alike this as if written together in a single query which won’t
work.

no matter what I request, I will always get the rank as 1

{1, "John Bellman", 39}

Thank you.

2

I believe solution will be the same as here How to get a row by id with row number in Ecto?

3

Thank you @fuelen
I update the query to use elixir map so that subquery will have access to it and filter based on the available
keys in the main query

user_query = from u in User, as: :user, windows: [
  w: [partition_by: u.category, order_by: [desc: u.activity_count]]
], select: %{rank: rank() |> over(:w), name: t.name, activity_rank: t.activity_count}

and the subquery looks like this

rank = 1
from user in subquery(user_query), where: user.rank == ^rank # reference rank key in user_query