How to query from database with comparing datetime

Questions / Help
1

I am trying to do a pagination base on datetime and when fetching next batch of record it return as expected but when going back it gives me unexpceted result. Below are the code before I explain more clearly what was the unexpected.

# lt = less than date
# gt = greater than date
def list_game_point(%{user_id: _, limit: _} = args):
  lt_date_query =
      if Map.has_key?(args, :lt_date) do
        dynamic([gp], gp.inserted_at < ^args.lt_date)
      else
        true
      end

    gt_date_query =
      if Map.has_key?(args, :gt_date) do
        IO.inspect Map.from_struct(args.gt_date)

        dynamic([gp], gp.inserted_at > ^args.gt_date)
      else
        true
      end

   __MODULE__.GamePoint
   |> limit(^args.limit)
   |> where(^lt_date_query)
   |> where(^gt_date_query)
   |> where([gp], gp.user_id == ^args.user_id)
   |> join(:inner, [gp], _ in assoc(gp, :op_user))
   |> join(:inner, [_, user], _ in assoc(user, :role))
   |> preload([_, user, role], [user: {user, role: role}])
   |> order_by(desc: :inserted_at)
   |> select([gp, _, _], map(gp, [
        :id,
        :amount,
        :inserted_at,
        op_user: [:id, :inserted_at, role: [:id]]
      ]))
   |> Repo.all()

On client side

// fetch initial data to render
const gtDate = result[0].inserted_at // first record and latest one
const ltDate = result[result.length -1].inserted_at // last record and the earliest one

// other body

// using react
// pass these to onClick button
// rightArrow and leftArrow function are dynamic and redefine on every batch record being fetched
// so that if we fetch next earliest record, the ltDate with become the gtDate of next 10 record
// whereas the gtDate will become the ltDate of next 10 record and so on
const rightArrow = function() {
  // fetch next 10 earliest record, which compare where less than last record of the fetched data
  setState({ gtDate: undefined, ltDate })
}

const leftArrow = functtion() {
  // fetch previous 10 or latest record, which compare where greater than first of the fetched data
  setState({ ltDate: undefined, gtDate })
}

Currently my dev database on a user has game point buying record of 26, so I am the admin checking for the game point of the user, and the limit of record was set to 10, initially the 10 record first return are latest 10 record, I click next I got the next 10, and another next I got 6, but when I click back to get the middle 10 to 20, i instead got the first 10 instead of the middle 10. The visualization are something like below (hope it helps understanding)

# since record are descending ordered, next will get next 10 earliest record
# the "next" got expected result but not the other way around
# paginate action and result
# initial          | next |               | next |
# |latest..earlist|(10)
#                      |latest..earliest|(10)
#                                             |latest..earliest|(6)

# paginate action and result
# initial          | next |               | next |
#                                         | back |
# |latest..earlist|(10)
#                      |latest..earliest|(10)
#                                             |latest..earliest|(6)
# |latest..earliest|(10) <- *** (instead got the first 10 batch, which result are wrong) ***
# and because I know total count of the user game point's record, I can go back another step which gives me empty array

So why fetching earliest record always return what I am expecting but not on the way back? Does my code wrong or how does datetime being compare in ecto which give me such result or any recommendation on how to do this better?

1 Like
2

For concreteness, let’s specify some values so they can be clearly referred to:

  • page 1 is from 2020-03-23 to 2020-02-28
  • page 2 is from 2020-02-28 to 2020-01-30
  • page 3 is from 2020-01-30 to 2020-01-01

when you’re looking at page 3, you have two dates available:

  • the beginning of the page, 2020-01-30
  • the end of the page, 2020-01-01

The end of the page isn’t useful for finding the previous page at all.

The beginning of the page can help, but note that this query won’t do what you’re expecting:

from(gp in __MODULE__.GamePoint,
  where: gp.inserted_at > ^~D(2020-01-30)
  order_by: [desc: gp.inserted_at]
  limit: ^args.limit
)

because it will (as you’ve reported) give you the most recent 10 records inserted after the target date.

You’ll have to reverse the result, but this query would do what you want:

from(gp in __MODULE__.GamePoint,
  where: gp.inserted_at > ^~D(2020-01-30)
  order_by: [asc: gp.inserted_at]
  limit: ^args.limit
)
3

I would recommend you to comment out everything, but dates and limits just to be able to debug it a little bit better.

You can also go a little deeper into Ecto and use Fragmets:

4

Yes thanks, I will try changing the order_by to see if I will get the correct result. When comparing thou I also take time into consideration not only just date.

5

Thanks, I will try it out.

6

Hi, I think solved the problem thanks to you. So here is what I add to make it works as what I expected:

def list_game_point(%{user_id: _, limit: _} = args):
# some body ...

  query =
      __MODULE__.InAppPoint
      |> limit(^args.limit)
      |> where(^lt_date_query)
      |> where(^gt_date_query)
      |> where([pp], pp.user_id == ^args.user_id)
      |> join(:inner, [pp], _ in assoc(pp, :op_user))
      |> join(:left, [_, user], _ in assoc(user, :roles))
      |> preload([_, user, role], [user: {user, roles: role}])
      |> select([pp, _, _], map(pp, [
          :id,
          :amount,
          :inserted_at,
          op_user: [:id, :inserted_at, roles: [:id]]
        ]))

    query =
      case [lt_date_query, gt_date_query] do
        [true, true] ->
          order_by(query, desc: :inserted_at)
        [_, true] ->
          order_by(query, asc: :inserted_at)
        _ ->
          order_by(query, desc: :inserted_at)
      end

    query
    |> Repo.all()
    |> Enum.sort(&(&1.inserted_at >= &2.inserted_at))

Could you please explain why order_by affect the result? I thought the order_by was just sorting the result set.