-3

Group rows by day/week/month using inserted_at column.
Example : If first row is created before 1 week then group by days and if any data is not created in middle date then add o as value.

[
 {"21 Jul" => 12},{"22 Jul" => 0},{"23 Jul" => 3}, {"24 Jul" => 1}...
 {"5 Aug" => 0}
]

Thanks in advance.

Ram
  • 7
  • 4

2 Answers2

1

Let's suppose you have Event schema with DateTime field called starts_at. To get, for example, events count by date you can make such function:

  def get_events_count_grouped_by_date(query) do
    query
    |> group_by([e], fragment("date(?)", e.starts_at))
    |> select([e], %{date: fragment("date(?)", e.starts_at), count: count(e.id)})
    |> order_by([e], asc: fragment("date(?)", e.starts_at))
  end

Then use it like this:

Event |> get_events_count_grouped_by_date() |> Repo.all()
Igor.Koshnarov
  • 11
  • 2
0

It's not quite clear from the question exactly what is required, but hopefully the following query helps: It will give you the count of records grouped by the date part of inserted_at.

defmodule CountByDateQuery do
  import Ecto.Query

  @doc "to_char function for formatting datetime as dd MON YYYY"
  defmacro to_char(field, format) do
    quote do 
      fragment("to_char(?, ?)", unquote(field), unquote(format))
    end
  end

  @doc "Builds a query with row counts per inserted_at date"
  def row_counts_by_date do
    from record in SomeTable,
    group_by: to_char(record.inserted_at, "dd Mon YYYY"),
    select: {to_char(record.inserted_at, "dd Mon YYYY"), count(record.id)}
  end

end

Usage:

row_counts_by_date() |> Repo.all() |> Map.new()
%{"05 Aug 2017" => 2}
Mike Buhot
  • 4,790
  • 20
  • 31
  • Thanks for answer. It returns correct results but my requirement is if any data in between has no data in db then add that date and count as 0. How to do achieve that ? – Ram Aug 08 '17 at 05:26
  • You could fill in any missing values with `Map.put_new` or do it in SQL by generating a series of dates and `left_join` to the query above, eg: https://stackoverflow.com/a/14113580/1650580 – Mike Buhot Aug 09 '17 at 01:26
  • Thank you very much – Ram Aug 10 '17 at 10:53