How to Best Group By a Particular Attribute of a Model/Table

Hello, I hope this post finds you well and in good spirits.

I am trying to use group_by on a query to group a set of events by the day each happens. Events have a name, address, city, state, zip, day of the week upon which they happen. They are weekly (farmers’ markets). Because Postgres isn’t a fan of group_by just one column, I’ve had to throw in the ID and that isn’t working as well as I’d like.

I just got through watching this video on selecting into a map, so I tried it:

iex(5)> markets = Market |> select([m], {m.day_of_week, m}) |> Repo.all() |> Enum.into(%{})
[debug] QUERY OK source="markets" db=0.9ms queue=0.1ms idle=1511.7ms
SELECT m0."id", m0."active", m0."city", m0."day_of_week", m0."name", m0."state", m0."street_address", m0."zip", m0."inserted_at", m0."updated_at", m0."day_of_week" FROM "markets" AS m0 []
  "Friday" => %MyApp.Markets.Market{
    __meta__: #Ecto.Schema.Metadata<:loaded, "markets">,
    active: false,
    city: "New Orleans",
    day_of_week: "Friday",
    id: 4,
    inserted_at: ~N[2021-04-13 18:30:15],
    name: "Nu Market 4",
    state: "LA",
    street_address: "777 Magazine Street",
    updated_at: ~N[2021-04-13 18:30:15],
    zip: "70130"
  "Saturday" => %MyApp.Markets.Market{
    __meta__: #Ecto.Schema.Metadata<:loaded, "markets">,
    active: false,
    city: "New Orleans",
    day_of_week: "Saturday",
    id: 2,
    inserted_at: ~N[2021-04-08 00:33:09],
    name: "Nu Market 2",
    state: "Louisiana (LA)",
    street_address: "4121 Canal Street",
    updated_at: ~N[2021-04-08 00:33:09],
    zip: "70119"

The end result is a map that uses the days of the week as a key. What will ultimately happen is I will have multiple entries in this map that have the same key.

My questions are:

  1. How would I go about writing the Ecto query to get the thing I actually want, which is the markets separated into groups based on the day upon which they occur?
  2. Is the query or the select-into-map the best way to go about this?

Thank you in advance.

Typically the GROUP BY clause in SQL is used more for generating aggregate data, but since you are wanting to get all of the information on each record, grouping in your application will probably prove more useful than grouping in the database. You can do this with Enum.group_by/2 rather than Enum.into/2:

markets =
  |> Repo.all()
  |> Enum.group_by(& &1.day_of_week)

So rather than select a list of tuples, this selects a list of structs, and then groups by the day_of_week field on that struct.

1 Like

Thank you so very much man. I super appreciate it.