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.
24.09.2016 techFor 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
Looks great!
Where would you put the
count_by_weekcode 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
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.2My 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