Filtering by datetime

[Florian Haase]

Hi

what would the recommended way to filter by datetime be?

All datetime values in VB are saved in a date and a time-field.

Is it possible in GraphQL to merge two fields and filter on the combination?

And how to find out what the max datetime in a given table is?

Florian 

[Marius Bancila]

FYI, the @export directive has been fixed. So if you want to take a solution that requires this directive, then it should work now.

[Marius Bancila · Accepted solution]

If you want to get the maximum or minimum change date/time of a record, you can also use a sort order and fetch only the top record. Here is how:

query($cid: Int) {
  useCompany(no: $cid) {   
    product(
      first: 1,
      sortOrder:{
      	changedDate : DESC
      	_thenBy :{
        	changedTime : DESC
      }
    })
    {
      items
      {
        changedDate
        changedTime
        
        changedDateTime
      }
    }
  }
}

[Florian Haase · Accepted solution]

Thank you, yes this would work too. 

[Marius Bancila · Accepted solution]

The fields changeDateTime and createdDateTime, as already mentioned, are the only ones that are composed from individual fields. As of now, the mentioned changeTimeInMs field (where available - in 11 tables) is not used.

These composed fields are not available for aggregates. Here, you can make two queries. You can use the @export directive. This does not work for you because there is a bug in our implementation, but the fix will be rolled out to production this week.

You can do the following:

query ($cid :Int!, $maxDate : Int = 0)
{
  useCompany(no: $cid)
  {   
    aggregate_date : order_aggregate
    {
      maximum
      {
        changedDate @export(as: "maxDate")
      }
    }
    
    aggregate_time : order_aggregate(filter: {changedDate :{_eq: $maxDate}})
    {
      maximum
      {
        changedTime
      }
    }
  }
}

In this example, I don't use a filter to get the maximum changedDate. But if you do, you need to include it also the second query.

[omelhus]

Any reason for not using chTmms where available?

[Marius Bancila]

ChTmms stores the same timestamp as ChDt and ChTm. Therefore, it does not bring anything new and is used in a single place (I've been told). Therefore, not really a field of much interest.

[omelhus]

Have a look in the date and time section of the documentation. You'll see how they translate changedDateTime. https://docs.business.visma.net/docs/schema/datetime.

@Marius Bancila Any reason you're not using chTmms where available?

[omelhus]

They are aliased to changedDateTime for most properties. They are not using chTmms, only chTm.

  order(
      filter: {
        _and: [
          { invoiceNo: { _not_eq: "" } }
          { invoiceCustomerNo: { _gt: 0 } }
          { changedDateTime: { _gt: $lastModified } }
        ]
      }
      after: $after
      sortOrder: { invoiceNo: ASC }
    )

[Florian Haase]

Yes, you are right.

I did not recognize that because I try to find the max-value of changedDateTime. So far the best idea was to sort by changedDateTime descending and take the first one.

But in the orderBy this alias is not available.

Next try is to use the aggregates, but also there alias is not available. 

Any further ideas?

Florian

[Florian Haase]

Maybe this could work?

query($maxDate: Int)
{
useCompany(no: 2136125) {
free1: freeInformation1_aggregate(filter:{_or:[{freeInformationType1:{_eq:2}},{freeInformationType1:{_eq:4}}]})
{
maximum
{
changedDate @export(as: "maxDate")
}
}
freeInformation1_aggregate(
filter:
{_and:[
{_or:[
{freeInformationType1:{_eq:2}}, {freeInformationType1:{_eq:4}}
]}
,
{changedDate: {_eq:$maxDate}}
]})
{
maximum
{
changedDate,
changedTime
}
}
}
}