How to do this ecto query?

Questions / Help
1

I have a query where I’m doing something like this

 query =
  from(
    u in User,
    where: u.user_id == ^user_id,
    group_by: [
      fragment("date_part(?,?)::int", "month", u.inserted_at),
      fragment("date_part(?,?)::int", "dow", t.inserted_at),
      u.user_id
    ],
    select: %{
      month: fragment("date_part(?,?)::int", "month", u.inserted_at),
      weekly: fragment("date_part(?,?)::int", "dow", t.inserted_at),
      monthly: count(u.user_id)
    }
  )

I’m trying to get a result where I want to know how many users are inserted in every month and how many on weekdays and weekends?

result will be something like this

[
  %{month: 10, users: 5, weekday: 2, weekend: 3},
  %{month: 9, users: 5, weekday: 1, weekend: 4}
]

The result I’m getting right now


[
  %{month: 10, users: 5, weekday: 2},
  %{month: 9, users: 5, weekday: 2}
]

It’s giving me the same weekday for every month

1 Like
2 
query =
  from(
    u in User,
    group_by: [
      fragment("date_part(?,?)::int", "month", u.inserted_at),
      fragment("date_part(?,?)::int >= 6", "isodow", u.inserted_at)
    ],
    select: %{
      month: fragment("date_part(?,?)::int", "month", u.inserted_at),
      weekend?: fragment("date_part(?,?)::int >= 6", "isodow", u.inserted_at),
      count: count(u.id)
    }
  )

This query will give you something like

[
  %{month: 10, weekend?: true, count: 3},
  %{month: 10, weekend?: false, count: 2},
  %{month: 9, weekend?: true, count: 4},
  %{month: 9, weekend?: false, count: 1}
]

Then you have to do some aggregation and transform in RAM to get the :users you want.

3 Likes