Calculating average number of sessions per user

I have the following resource:

defmodule MyApp.Ash.Dashboards.SessionEvent do
  @moduledoc """
  Resource that maps to Analytics DB session_events materialized view
  """

  use Ash.Resource,
    domain: MyApp.Ash.Dashboards,
    data_layer: AshPostgres.DataLayer

  postgres do
    table "session_events"
    repo MyApp.Dashboards.AnalyticsRepo
  end

  resource do
    require_primary_key? false
  end

  code_interface do
    [...]
  end

  actions do
    defaults [:read]

    [...]
  end

  attributes do
    attribute :id, :string

    attribute :collection_title, :string
    attribute :completed_project_title, :string
    attribute :content_entity_title, :string
    attribute :email, :string
    attribute :event_duration, :decimal
    attribute :event_text, :string
    attribute :is_last_event_of_session, :integer
    attribute :name, :string
    attribute :timestamp, AshPostgres.Timestamptz
    attribute :user_id, :string
    attribute :viewed_project_title, :string
  end
end

defmodule MyApp.Ash.Dashboards.SessionEvent do
  @moduledoc """
  Resource that maps to Analytics DB session_events materialized view
  """

  use Ash.Resource,
    domain: MyApp.Ash.Dashboards,
    data_layer: AshPostgres.DataLayer

  postgres do
    table "session_events"
    repo MyApp.Dashboards.AnalyticsRepo
  end

  resource do
    require_primary_key? false
  end

  code_interface do
    [...]
  end

  actions do
    defaults [:read]

    [...]
  end

  attributes do
    attribute :id, :string

    attribute :collection_title, :string
    attribute :completed_project_title, :string
    attribute :content_entity_title, :string
    attribute :email, :string
    attribute :event_duration, :decimal
    attribute :event_text, :string
    attribute :is_last_event_of_session, :integer
    attribute :name, :string
    attribute :timestamp, AshPostgres.Timestamptz
    attribute :user_id, :string
    attribute :viewed_project_title, :string
  end
end

What I'm trying to do is calculate the average number of sessions per user. The SQL query we currently use for this is this:

select avg(num_sessions) as average_sessions
from (
        select user_id, count(user_id) num_sessions
        from session_events 
        where is_last_event_of_session = 1
        group by user_id
     )
;

select avg(num_sessions) as average_sessions
from (
        select user_id, count(user_id) num_sessions
        from session_events 
        where is_last_event_of_session = 1
        group by user_id
     )
;

Where num_sessions is the alias of a subquery, e.g. select user_id, count(user_id) AS num_sessions. I was told that this would need to be a calculation but I don't really know how to proceed with that.

14 Replies

ken-kost•2w ago

what you want is an aggregate since user has many session events you could give user resource an aggregate:

aggregates do
  avg :average_number_of_sessions, :session_events do
    filter expr(is_last_event_of_session == 1)
  end
end

aggregates do
  avg :average_number_of_sessions, :session_events do
    filter expr(is_last_event_of_session == 1)
  end
end

something like that maybe

EgeOP•2w ago

That results in an error that complains about a missing relationship:

10:40:50.930 [error] Process #PID<0.10410.0> raised an exception
** (Spark.Error.DslError) [MyApp.Ash.Dashboards.SessionEvent]
aggregates -> average_number_of_sessions:
  relationship referenced in aggregate `MyApp.Ash.Dashboards.SessionEvent.session_events` does not exist

10:40:50.930 [error] Process #PID<0.10410.0> raised an exception
** (Spark.Error.DslError) [MyApp.Ash.Dashboards.SessionEvent]
aggregates -> average_number_of_sessions:
  relationship referenced in aggregate `MyApp.Ash.Dashboards.SessionEvent.session_events` does not exist

ken-kost•2w ago

you also need the relationship: has_many :session_events, MyApp.Ash.Dashboards.SessionEvent hence why I said since user has many session events 😅 I thought you already defined that relationship

EgeOP•2w ago

Define the relationship on the User resource?

ken-kost•2w ago

yes. is it true that user has many sessions?

EgeOP•2w ago

Yes. But they are on another database & repo So there's no direct foreign-key association like you would normally have in a scenario like this

ken-kost•2w ago

🤔 that's interesting. did you try? what happens? just to check, you have two resources, user and session_event. one is looking at one db, the other on another, right?

EgeOP•2w ago

correct

ken-kost•2w ago

I got a feeling it should still work session event has a column user_id which is the same as id of user from the other db do try and let me know, now I'm curious 🤠

EgeOP•2w ago

Yesterday I tried it like this:

read :average_sessions_per_student do
  argument :after, :utc_datetime_usec, allow_nil?: true
  argument :before, :utc_datetime_usec, allow_nil?: true

  prepare fn query, context ->
    require Ash.Query

    query
    |> Ash.Query.aggregate(:num_sessions, :count, :user)
  end
end

read :average_sessions_per_student do
  argument :after, :utc_datetime_usec, allow_nil?: true
  argument :before, :utc_datetime_usec, allow_nil?: true

  prepare fn query, context ->
    require Ash.Query

    query
    |> Ash.Query.aggregate(:num_sessions, :count, :user)
  end
end

relationships do
  belongs_to :user, User do
    public? true
    source_attribute :user_id
    destination_attribute :id
    validate_destination_attribute? false
  end
end

relationships do
  belongs_to :user, User do
    public? true
    source_attribute :user_id
    destination_attribute :id
    validate_destination_attribute? false
  end
end

This resulted in:

{:error,
 %Ash.Error.Unknown{
 bread_crumbs: ["Error returned from: MyApp.Ash.Dashboards.SessionEvent.average_sessions_per_student"], 
 query: "#Query<>", 
 errors: [
   %Ash.Error.Unknown.UnknownError{
     error: "** (Postgrex.Error) ERROR 42P01 (undefined_table) relation \"public.users\" does not exist\n\n    query: SELECT s0.\"id\", s0.\"name\", s0.\"timestamp\", s0.\"email\", s0.\"user_id\", s0.\"collection_title\", s0.\"content_entity_title\", s0.\"event_text\", s0.\"completed_project_title\", s0.\"event_duration\", s0.\"is_last_event_of_session\", s0.\"viewed_project_title\", coalesce(s1.\"num_sessions\"::bigint, $1::bigint)::bigint FROM \"session_events\" AS s0 LEFT OUTER JOIN LATERAL (SELECT su0.\"id\" AS \"id\", coalesce(count(*), $2::bigint)::bigint AS \"num_sessions\" FROM \"public\".\"users\" AS su0 WHERE (s0.\"user_id\" = su0.\"id\") GROUP BY su0.\"id\") AS s1 ON TRUE",
     field: nil,
     value: nil,
     splode: Ash.Error,
     bread_crumbs: ["Error returned from: MyApp.Ash.Dashboards.SessionEvent.average_sessions_per_student"],
     vars: [],
     path: [],
     stacktrace: #Splode.Stacktrace<>,
     class: :unknown
   }
 ]
 }}

{:error,
 %Ash.Error.Unknown{
 bread_crumbs: ["Error returned from: MyApp.Ash.Dashboards.SessionEvent.average_sessions_per_student"], 
 query: "#Query<>", 
 errors: [
   %Ash.Error.Unknown.UnknownError{
     error: "** (Postgrex.Error) ERROR 42P01 (undefined_table) relation \"public.users\" does not exist\n\n    query: SELECT s0.\"id\", s0.\"name\", s0.\"timestamp\", s0.\"email\", s0.\"user_id\", s0.\"collection_title\", s0.\"content_entity_title\", s0.\"event_text\", s0.\"completed_project_title\", s0.\"event_duration\", s0.\"is_last_event_of_session\", s0.\"viewed_project_title\", coalesce(s1.\"num_sessions\"::bigint, $1::bigint)::bigint FROM \"session_events\" AS s0 LEFT OUTER JOIN LATERAL (SELECT su0.\"id\" AS \"id\", coalesce(count(*), $2::bigint)::bigint AS \"num_sessions\" FROM \"public\".\"users\" AS su0 WHERE (s0.\"user_id\" = su0.\"id\") GROUP BY su0.\"id\") AS s1 ON TRUE",
     field: nil,
     value: nil,
     splode: Ash.Error,
     bread_crumbs: ["Error returned from: MyApp.Ash.Dashboards.SessionEvent.average_sessions_per_student"],
     vars: [],
     path: [],
     stacktrace: #Splode.Stacktrace<>,
     class: :unknown
   }
 ]
 }}

So it's trying to join the tables and failing beacuse the table of the relationship doesn't exist in the database that the resource is in

ken-kost•2w ago

okay, so there is Custom aggregates in the query i.e. in opts for aggregate/4 you can give query; you could construct your query regarding sessions, check if the query works. add filtering to it regarding user ids and what not. give that query for the aggregate call. maybe that could work 🤔

EgeOP•2w ago

But that doesn't solve the relationship problem, i.e. aggregates seem to require a relationship to be defined, and in this case we cannot define one because the other table is in another database

ken-kost•2w ago

🤔 yea... could you post the postgres sections from your resources? just to check another idea 💡 there is Manual Relationship maybe with that you could "load" the appropriate sessions from the other database you would have

has_many :session_events, MyApp.Ash.Dashboards.SessionEvent do
  manual MyApp.Ash.Dashboards.SessionEventManual
end

has_many :session_events, MyApp.Ash.Dashboards.SessionEvent do
  manual MyApp.Ash.Dashboards.SessionEventManual
end

then you would create that module, use ManualRelationship and implement your load that queries your other database. then you could use that relationship in an aggregate as mentioned above

ZachDaniel•2w ago

Do you need to be able to filter on this value? Or are you just looking to compute it? Ash doesn't have grouping except in aggregates, you'd use Ecto for that.

def calculate(records, _, _) do
  # query = make an Ecto query 
  # results = run the query, grouped by record id
  # {:ok, Enum.map(records, &results[&1.id])}
end

def calculate(records, _, _) do
  # query = make an Ecto query 
  # results = run the query, grouped by record id
  # {:ok, Enum.map(records, &results[&1.id])}
end

Something along those lines in a calculation If you want to filter on it, then it's a fragment with a subquery unfortunately fragment("(SELECT ...)")

Gaming

Programming

Calculating average number of sessions per user

Did you find this page helpful?