Hey I have a table of notifications with a reference_id and inserted_at, there can be multiple notifications with the same reference_id.
How can I do pagination based on that, that all the items with the same reference id and the same date count as one in terms of pagination?
so if we would have:
notification_1 = %{
id: 1,
reference_id: 2,
inserted_at: 2019-06-24 07:11:49+00
},
notification_2 = %{
id: 2,
reference_id: 2,
inserted_at: 2019-06-24 08:11:49+00
}
so these 2 would count as 1 because they are from the same day and have the same inserted_at day
so in this case if I would send limit: 1 i would get both
Paginate over a query with the following. You could either try e.g. json_agg for getting all the notifications or just array_agg for the ids and do a second query to load the notifications for the selected ids.
why do i get this error if i want to sort by inserted_at before grouping?
ERROR 42803 (grouping_error) column "n0.inserted_at" must appear in the GROUP BY clause or be used in an aggregate function
query: SELECT array_agg(DISTINCT n0."id") FROM "notifications" AS n0 WHERE (n0."user_id" = $1) GROUP BY date_trunc('day', n0."inserted_at"), n0."reference", n0."reference_id" ORDER BY n0."inserted_at" DESC LIMIT $2 OFFSET $3
Show only app frames
I want to have the starting order be by the newest to the oldest
There are two things here to handle: Order of groups (outer list) and order of the aggregates. I think the latter you can ignore in the first query, as you can order them always in the second one. For the first part you need to use an aggregate to group_by. There no longer exists just a single “inserted_at” field per result row, which sql could order by.
It contains one or more notifications. You need to decide how you create one timestamp to order by from those. Above I used min to simply use the smallest timestamp of the group to order the groups by.
seems like the reference introduces the problem, since it is possible to have the same reference_id but a different reference, so i group by that as well
but shouldn’t the order_by date annul that problem? if it would order the groups it shouldn’t matter that there are more groups
for context, I have this function that takes the result of this query and then groups the notifications, but that messes up the pagination:
defp group_messages(notifications) do
# Split messages from the other notification types and add some data to help further processing
{messages, others} =
notifications
|> Enum.scan([], fn message, _acc ->
date_string =
case Timex.format(message.inserted_at, "{YYYY}-{0M}-{0D}") do
{:ok, date_string} -> date_string
_ -> "no date"
end
message
|> Map.put(:total_message_count, nil)
|> Map.put(:date, date_string)
|> Map.put(
:milliseconds,
DateTime.to_unix(Timex.to_datetime(message.inserted_at), :millisecond)
)
end)
|> Enum.split_with(fn n -> n.reference == "topic" end)
# Sort to latest message
# Group the messages by date
# Make the notifictations unique, so we only get one notification with the latest message
# Flatten all into one list
# Give each unique notification the amount of messages by day and refrence id
# Profit!
grouped_messages =
messages
|> Enum.sort_by(& &1.milliseconds, &>/2)
|> Enum.group_by(fn message -> message.date end, fn message -> message end)
|> Enum.map(fn {_date, gm} -> Enum.uniq_by(gm, fn m -> m.reference_id end) end)
|> List.flatten()
|> Enum.scan([], fn message, _acc ->
count =
Enum.count(messages, fn m ->
m.date == message.date and m.reference_id == message.reference_id
end)
Map.put(message, :total_message_count, count)
end)
# Merge them all back together and sort to latest
(others ++ grouped_messages)
|> Enum.sort_by(& &1.milliseconds, &>/2)
end
so I’d need this partially reproduced on the db level to make the pagination works, and maybe in the future simplify this function
if i add n.inserted_by then I am able to do order_by: [desc: :inserted_at]
and then it is ordered, but then we have bad groups since grouping by just inserted_at is stricter than the fragment.
This works because you’re grouping by the value you’re ordering by. This is no longer the case if you group by just the date of insertion and not the whole timestamp. On the other hand I’ve to admit I didn’t try the order_by I suggested specifically.
