Elixir Ecto - how to group and count records by week

For a recent project’s api, I had to group and count records by week, on a field I could dynamically assign. This what I came up with:

@doc "Count responses per week on `date_field` (atom)"
def count_by_week(query, date_field) do
    query
    |> group_by([r], (fragment("date_part('week', ?)", (field(r, ^date_field)))))
    |> select([r], [(fragment("date_part('week', ?)", (field(r, ^date_field)))), count("*")])
end

As you can see I use the ecto fragment/2 macro. It takes a date_part and a value. In this particular case I needed to be able to set what date_field to select (formatted as Ecto.DateTime), for which I use field/2. You could of course also do this for the type of date part. See the postgresql docs for more information on which kinds of date_part there are.

So, in practise, the above query will look like:

# example model is called `Response`
def fetch_data(id) do
    Response
    |> Response.with_id(id)
    |> Response.count_by_week(:inserted_at)
    |> Repo.all
    |> Enum.map(fn([weeknr,count]) -> ["Week #{weeknr}", count] end)
end

Links