Cannot query on a date range, get back no results each time

I'm having a hard time understanding why I keep getting 0 results back from a query I am trying to perform. Basically I am trying to return only results within a date range. On a given table I have a createdAt which is a DateTime scalar. This basically gets automatically filled in from prisma (or graphql, not sure which ones sets this). So on any table I have the createdAt which is a DateTime string representing the DateTime when it was created.

Here is my schema for this given table:

type Audit {
    id: ID! @unique
    user: User!
    code: AuditCode!
    createdAt: DateTime!
    updatedAt: DateTime!
    message: String
}

I queried this table and got back some results, I'll share them here:

"getAuditLogsForUser": [
      {
        "id": "cjrgleyvtorqi0b67jnhod8ee",
        "code": {
          "action": "login"
        },
        "createdAt": "2019-01-28T17:14:30.047Z"
      },
      {
        "id": "cjrgn99m9osjz0b67568u9415",
        "code": {
          "action": "adminLogin"
        },
        "createdAt": "2019-01-28T18:06:03.254Z"
      },
      {
        "id": "cjrgnhoddosnv0b67kqefm0sb",
        "code": {
          "action": "adminLogin"
        },
        "createdAt": "2019-01-28T18:12:35.631Z"
      },
      {
        "id": "cjrgnn6ufosqo0b67r2tlo1e2",
        "code": {
          "action": "login"
        },
        "createdAt": "2019-01-28T18:16:52.850Z"
      },
      {
        "id": "cjrgq8wwdotwy0b67ydi6bg01",
        "code": {
          "action": "adminLogin"
        },
        "createdAt": "2019-01-28T19:29:45.616Z"
      },
      {
        "id": "cjrgqaoreoty50b67ksd04s2h",
        "code": {
          "action": "adminLogin"
        },
        "createdAt": "2019-01-28T19:31:08.382Z"
      }]

Here is my getAuditLogsForUser schema definition

getAuditLogsForUser(userId: String!, before: DateTime, after: DateTime): [Audit!]!

So to test I would want to get all the results in between the last and first.

2019-01-28T19:31:08.382Z is last 2019-01-28T17:14:30.047Z is first.

Here is my code that would inject into the query statement:

if (args.after && args.before) {
                where['createdAt_lte'] = args.after;
                where['createdAt_gte'] = args.before;
            }

            console.log(where)

            return await context.db.query.audits({ where }, info);

In playground I execute this statement

getAuditLogsForUser(before: "2019-01-28T19:31:08.382Z" after: "2019-01-28T17:14:30.047Z") { id code { action } createdAt }

So I want anything that createdAt_lte (less than or equal) set to 2019-01-28T17:14:30.047Z and that createdAt_gte (greater than or equal) set to 2019-01-28T19:31:08.382Z

However I get literally no results back even though we KNOW there is results.

I tried to look up some documentation on DateTime scalar in the graphql website. I literally couldn't find anything on it, but I see it in my generated prisma schema. It's just defined as Scalar. With nothing else special about it. I don't think I'm defining it elsewhere either. I am using Graphql-yoga if that makes any difference.

(generated prisma file)
scalar DateTime

I'm wondering if it's truly even handling this as a true datetime? It must be though because it gets generated as a DateTime ISO string in UTC.

Just having a hard time grasping what my issue could possibly be at this moment, maybe I need to define it in some other way? Any help is appreciated

Solution 1:

Sorry I misread your example in my first reply. This is what you tried in the playground correct?

getAuditLogsForUser(
   before: "2019-01-28T19:31:08.382Z",
   after: "2019-01-28T17:14:30.047Z"
  ){ 
  id 
  code { action }
  createdAt 
}

This will not work since before and after do not refer to time, but are cursors used for pagination. They expect an id. Since id's are also strings this query does not throw an error but will not find anything. Here is how pagination is used: https://www.prisma.io/docs/prisma-graphql-api/reference/queries-qwe1/#pagination

What I think you want to do is use a filter in the query. For this you can use the where argument. The query would look like this:

getAuditLogsForUser(
      where:{AND:[
           {createdAt_lte: "2019-01-28T19:31:08.382Z"},
           {createdAt_gte: "2019-01-28T17:14:30.047Z"}
         ]}
   ) {
     id 
     code { action } 
     createdAt 
}

Here are the docs for filtering: https://www.prisma.io/docs/prisma-graphql-api/reference/queries-qwe1/#filtering