Pagination based on date and reference

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

for the array_agg see:

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.

group_by: [n.reference_id, fragment("date_trunc(?, ?)", "day", n.inserted_at)]

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

order_by: min(n.inserted_at)

If there are possibly multiple values per row you need to tell the db which one to use for the ordering.

1 Like

ok my bad here, although interesting that this didnt work:

from(n in query, order_by: [desc: :inserted_at])

but this doesn’t fix the problem, regarding the order that the lists come out of the grouping

    from(n in query,
      group_by: [
        fragment("date_trunc(?, ?)", "day", n.inserted_at)
      select: fragment("array_agg(DISTINCT ?)",

the order seems to be this every time:

[[1], [9], [10, 11, 12], [13, 14, 15, 16, 17]] - the problem seems to be that they are on the same day so the date checking has no effect in this case

so the grouping is correct just would need the order of the groups to be by the actual inserted_at

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.

1 Like

but what would that first aggregate contain that i could group_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.

well i think i would want to have the newest one as the representative time from each group

Then use max() instead of min()

hmm, then i am a bit confused, where should i do the order_by?

 from(n in query,
      group_by: [
        fragment("date_trunc(?, ?)", "day", n.inserted_at)
      order_by: max(n.inserted_at),
      select: fragment("array_agg(DISTINCT ?)",

im getting the same result with max and min :smile:
isn’t that a problem here that the date gets truncated before the ordering?

They shouldn’t. They’re grouped by the truncated date, but the date itself should stay the same.

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} =
      |> Enum.scan([], fn message, _acc ->
        date_string =
          case Timex.format(message.inserted_at, "{YYYY}-{0M}-{0D}") do
            {:ok, date_string} -> date_string
            _ -> "no date"

        |> Map.put(:total_message_count, nil)
        |> Map.put(:date, date_string)
        |> Map.put(
          DateTime.to_unix(Timex.to_datetime(message.inserted_at), :millisecond)
      |> 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 =
      |> Enum.sort_by(& &1.milliseconds, &>/2)
      |> Enum.group_by(fn message -> end, fn message -> message end)
      |> {_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 ->
   == and m.reference_id == message.reference_id

        Map.put(message, :total_message_count, count)

    # Merge them all back together and sort to latest
    (others ++ grouped_messages)
    |> Enum.sort_by(& &1.milliseconds, &>/2)

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.

rom(n in query,
      group_by: [
        # fragment("date_trunc(?, ?)", "day", n.inserted_at),
        # n.reference
      order_by: [desc: :inserted_at],
      select: fragment("array_agg(DISTINCT ?)",

so it really seems like that this:

order_by: max(n.inserted_at),

doesn’t want to work for me

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.

1 Like

is it not possible to do ORDER BY in the fragment?