GraphQL - Getting the datetime that a category was added to a ticket

It’s important for us to know what time a ticket was tagged with a category (we use it for SLAs).
I have a brute force method I’m using now (cron jobbing all open tickets every 5mins)…

But wondering if GQL could solve this more elegantly.

I see now there is a created_at and updated_at inside the ticket_category object…

BUT… those dates are when the category itself was created, NOT when the relationship to the ticket was created.

So the query I was trying to find is…

"Tell me all the tickets who had a ticket_category added to it within the last hour"
Is this possible?

The query below was my attempt, but its returning based on the date that category was created, initially (not in relation to the ticket)

query q($rrf:ReverseRelationFilter){
  tickets( reverse_relation_filters:[$rrf]){
                   entities{
                    id, subject
                    ticket_categories{
                      entities{
                        id, name, updated_at, created_at
                      }
                    }
                   }
  }}

Variables:

{
  "rrf":{
    "relation": "ticket_categories",
    "search": [
      { 
        "datetime_fields": [
          {
                "operator": "GT",
            "attribute": "created_at",
            "search_value": "2020-12-10 06:00:56"
          }
        ]
      }
    ]
  }
}

Relay’s Cursor Connections spec allows for this sorta thing - instead of putting a direct list of the connected entities inside of Connection objects like Sonar’s does, it wraps them with an Edge which allows for attaching additional metadata, such as when a relationship was created.

Doing a reverse relation filter on the Logs entity for its logged_entity_type being equal to TicketCategory should be what you’re looking for.

        query q($rrf: ReverseRelationFilter) {
      tickets(reverse_relation_filters: [$rrf]) {
        entities {
          id
          subject
          logs {
            entities {
              logged_entity_type
            }
          }
          ticket_categories {
            entities {
              id
              name
              updated_at
              created_at
            }
          }
        }
      }
    }

   {
      "rrf": {
        "relation": "logs",
        "search": [
          {
            "string_fields": [
              {
                "attribute": "logged_entity_type",
                "match": true,
                "search_value": "TicketCategory"
              }
            ],
            "datetime_fields": [
              {
                "operator": "GT",
                "attribute": "created_at",
                "search_value": "2020-12-10 06:00:56"
              }
            ]
          }
        ]
      }
    }

I highly recommend taking a look at the types of data in the Log object. They exist on every entity within Sonar and provide a ton of useful data.

Hope this helps! :smile: