GraphQL filter with OR Issue

[JBrake]

I am using GraphQL to load a list of objects that have the publish_status of "published" AND where the publish_date is null OR the publish_date is less than or equal to today, AND where the expiry_date is null OR the expiry_date is greater than or equal to tomorrow. 

Is this even possible? I've tied every combination I can think of but cannot get the out come I need. 

Has anyone done some thing like this sucessfully before?

# $today: {{ unixtimestamp(today() }}
# $tomorrow {{ unixtimestamp(today().plusDays(1)) }}


query LoadMyObjectList($today: Date, $tomorrow:Date) {
  CRM {
    MyObjects: p_myobject_collection(
      orderBy: start_date__desc
      offset: $page
      limit: $limit
      filter: {publish_status__eq: "published", OR: [{publish_date__lte: $today, publish_date__null: true},{expiry_date__gte: $today, expiry_date__null: true}]}}
    ) {
      total
      limit
      offset
      items {
        hs_object_id
        name
        description
        link
        publish_status
        publish_date
        start_date
      }
    }
  }
}

Jonathan
 

[JBrake · Accepted solution]

Hey ya,

Sorry for the late reply, our friday come early.

plusDays / plus_time didn't make a difference an the output is the same. I have managed to identify the problem though, and have implemented a workaround with an additional property and a workflow, while inforcing that a date is entered into the two date fields I am querying on.

The  problem it seams is this:

publish_date__null: true, expiry_date__null: true

You cannot use __null or __not_null to check for null values in date fields.

I reckon you should be, null is a valid (non)value for any field.

Jonathan

[Kevin-C]

Hey @JBrake 

So I haven't tested this, but in theory the following query should represent this logical expression:

publish_status="published" AND (

  (publish_date <= $today AND expiry_date >= $tomorrow)

  OR

  (publish_date <= $today AND expiry_date == null)

  OR

  (publish_date ==  null AND expiry_date >= $tomorrow)

  OR

  (publish_date == null AND expiry_date == null)

)

# $today: {{ unixtimestamp(today()) }}
# $tomorrow {{ unixtimestamp(today().plusDays(1)) }}

query LoadMyObjectList($today: Date, $tomorrow: Date) {
  CRM {
    MyObjects: p_myobject_collection(
      orderBy: start_date__desc
      offset: $page
      limit: $limit
      filter: {publish_status__eq: "published",
        OR: [
          {publish_date__lte: $today, expiry_date__gte: $tomorrow},
          {publish_date__lte: $today, expiry_date__null: true},
          {publish_date__null: true, expiry_date__gte: $tomorrow},
          {publish_date__null: true, expiry_date__null: true},
        ]}
    ) {
      total
      limit
      offset
      items {
        hs_object_id
        name
        description
        link
        publish_status
        publish_date
        start_date
      }
    }
  }
}

I believe this is what you're looking for.

You also had a missing ")" when defining your $today variable

Hopefully this gets you moving!

If I can clarify anything please don't hesitate to reach out!

[JBrake]

Thanks for the prompt reply, yeah I tried that and get the error:

"errors": [
    {
      "message": "Exception while fetching data (/CRM/MyObjectsCollection) : No value present",
      "locations": [
        {
          "line": 19,
          "column": 5,
          "sourceName": null
        }
      ],
      "path": [
        "CRM",
        "MyObjectsCollection"
      ],
      "extensions": null,
      "errorType": "DataFetchingException"
    }
  ]

Jonathan

[Kevin-C]

Heu @JBrake 

The "No value present" error typically occurs in GraphQL when the query attempts to access a value that is either missing or not present in the context it expects.

I would check that all variables are recieving the expected values:

• $today
• $tomorrow
• $page
• $limit

After a quick review when defining $tomorrow you might try the plus_time filter rather than the plusDays function. You may also need to throw a render filter on those today functions to ensure the correct computation order.

EDIT: I would also remove the filters slowly to id which is throwing the error.