GraphQL filter with OR Issue

Developer Support CMS Development
1

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

2

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!

3

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

4

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.

5

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

``