Turn this query into a preload

Phoenix Forum Questions / Help
, ,
1

I have schema a Class has_many User through Enrollment. Currently I have a single enrollments join table, that holds additional data like processed_at parameter.

I am displaying to a user a list of his classes, and need to display his rank (in fact row_number) in each class, which is derived from processed_at parameter. i.e. something like “you are 5th in class Foo, and 7th in class Bar”.

In my live view I am grabbing the user and his enrollments separately:

current_user = Accounts.get_user_by_session_token(session["user_token"])
current_user_enrollments = Accounts.get_user_enrollments(current_user.id)

the first one is standard phoenix auth function, the second one is the more tricky one:

def get_user_enrollments(user_id) do
    enrollments_with_rank =
      from e in Enrollment,
      select: e,
      # this partitions e by class_id and assigns row_number within that partition
      select_merge: %{rank: over(row_number(), partition_by: e.class_id, order_by: e.processed_at)}

    user_enrollments =
      from ex in subquery(enrollments_with_rank),
      where: ex.user_id == ^user_id

    Repo.all(user_enrollments)

  end

I can’t seem to figure out a syntax that would allow me to do this within a preload. This is a function I tried to create:

def preload_ranked_enrollments(user) do
    enrollments_with_rank =
      from e in Enrollment,
      select: e,
      select_merge: %{rank: over(row_number(), partition_by: e.class_id, order_by: e.processed_at)} # assigns rank by processed_at

    user
    |> Repo.preload(enrollments: enrollments_with_rank)
    |> Repo.preload(enrollments: [:class])
  end

and then call it from liveview this way:

current_user = Accounts.get_user_by_session_token(session["user_token"])
    |> Accounts.preload_ranked_enrollments()

However, it seems like the query in preload_ranked_enrollments actually somehow applies where: e.user_id == ^(user.id) because it always returns all enrollments with rank 1. Meaning if I got enrollments with given class_id and user_id, it would always return just 1 row and obviously give it rank 1. Is this something that Repo.preload actually does? Does it somehow inject that additional where clause into the query?

Note: I use terms “rank” and “row_number” interchangeably here, even though in postgres these are two different things and I am aware of the difference. I initially used the term “rank” in my schema, but getting row_number (always unique) rather than rank from postgres. Will probably change this to avoid confusion in code in future.

Thank you.

2

yes, you can inspect this in logs.
Try to wrap enrollments_with_rank in preload_ranked_enrollments function into a subquery, as you did in your first example.

3

Thank you for the suggestion, it seems like that works:

def preload_ranked_enrollments(user) do
    enrollments_with_rank =
      from e in Enrollment,
        select: e,
        # assigns rank by processed_at
        select_merge: %{
          rank: over(row_number(), partition_by: e.class_id, order_by: e.processed_at)
        }

    user_enrollments =
      from ex in subquery(enrollments_with_rank),
        where: ex.user_id == ^(user.id)

    user
    |> Repo.preload(enrollments: user_enrollments)
    |> Repo.preload(enrollments: [:class])
  end