Glenn Jones

Hello 👋 Welcome to my corner of the internet. I write here about the different challenges I encounter, and the projects I work on. Find out more about me.

Elixir Ecto - how to group and count records by week

24.09.2016
 tech

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

1Augustin Riedinger

Looks great!

Where would you put the count_by_week code part so that it is available for every model of the project?

Also, it would make sense to sort the values by week number, wouldn't it?

Thanks

Glennauthor

Hi Augustin,

This was placed in a specific model, so ./web/models/model_name.ex, where I have a section for component queries, and one for full queries. This is old elixir & phoenix though: elixir 1.2 and phoenix 1.2.0, I think the file organisation has changed quite a bit since 1.2

My specific use case didn't need to sort by week number, but yes you could, have a look at https://hexdocs.pm/ecto/Ecto.Query.html#order_by/3 ! I haven't touched this project in a long time, but I would be happy to have a look at it later to figure it out