My Products
Help
Florian Haase
PARTNER

Filtering by datetime

by 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 

10 REPLIES 10

by 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.

Accepted solution
Marius Bancila
VISMA

by Marius Bancila

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
      }
    }
  }
}
Accepted solution
Florian Haase
PARTNER

by Florian Haase

Thank you, yes this would work too. 

Accepted solution
Marius Bancila
VISMA

by Marius Bancila

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
PARTNER

by omelhus

Any reason for not using chTmms where available?

by 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
PARTNER

by omelhus (Updated ‎08-11-2022 10:41 by omelhus PARTNER )

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
PARTNER

by 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
PARTNER

by 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
PARTNER

by 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
}
}
}
}