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"
}
}
iex(6)>
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:
- 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?
- Is the query or the select-into-map the best way to go about this?
Thank you in advance.