Query filter by date is not working. Please help me

Hi,

I need a query in which I can filter items by multiple dates. By example, I have an array of dates and I want to retrieve items where the column date4 is exactly that date. Because of that the trick with using between operator doesn’t work.

As far as I know there are two queries for filtering items by column dates. Unfortunately non of them work properly.

  1. items_page_by_column_values. When I use this query the API returns only the first item. See example below:
query {
  items_page_by_column_values (
    board_id: 5600134463, 
    columns: [
      {column_id: "date4", column_values: ["2022-11-01", "2023-11-30", "2023-11-05"]}
    ],
    limit: 100
  ) {
    cursor
    items { 
        id 
        name 
        updated_at 
        column_values(ids: ["date4"]) {
          id 
          value
          text
          type      
        } 
      }
    	cursor 
  }
}

In my test board I have 3 items that have above listed dates (["2022-11-01", "2023-11-30", "2023-11-05"]) inside the date4 column. The API returns only the first one. If I do 3 API calls for each date then I will get all 3 items but potentially I can have 50 dates to filter by and I want to avoid making 50 API calls.

  1. items_page with query_params. This doesn’t work at all in conjunction with any_of operator. It simply returns empty list. I does returns something when I use a single date with greater_than_or_equals operator but that isn’t what I am looking for.
query
{ 
    
  boards(ids: ["5600134463"]) {
  
    items_page( limit: 100
      query_params: 
      {
        rules: [{
          column_id: "date4",
          compare_value: ["2022-11-01", "2023-11-30", "2023-11-05"], 
          operator: any_of
        }]
      } 
    )
    {
      items { 
        id 
        name 
        updated_at 
        column_values(ids: ["date4"]) {
          id 
          value
          text
          type      
        } 
      }
    	cursor 
    } 
  } 
}

I also tried using ["EXACT", "2022-11-01"] but that also doesn’t work with any_of operator. To me this looks like a huge bug and I don’t think I am the only one who is experiencing it.

How can filter by date be so hard to make it work? I really want to make as less API calls as possible and ability to filter by date would help me go this path.

Thanks,
Josip

Hi Josip,

For items_page_by_column_values, this does only allow for one string value to be passed in, as stated in our documentation here, if you are not using the between operator to find a range. I can raise a feature request to be able to filter using multiple column values and finding any values that match. Thank you for the feedback!

Best,
Joseph

1 Like

Hi Joseph,

Now I see that filtering by date is only supported for a single date value, thanks for pointing me out this. I am not sure how I missed that.

I would appreciate it if you can raise up a feature request to enable filtering by multiple date values!

What about items_page with query_params? How come that filtering by date isn’t working there?

Thanks,
Josip

Hi Josip,

For the items_page query with query_params, can you try adding “EXACT” to the compare_value and changing the operator to greater_than?

query
{ 
    
  boards(ids: ["5600134463"]) {
  
    items_page( limit: 100
      query_params: 
      {
        rules: [{
          column_id: "date4",
          compare_value: ["EXACT", "2022-11-01", "2023-11-30", "2023-11-05"], 
          operator: greater_than
        }]
      } 
    )
    {
      items { 
        id 
        name 
        updated_at 
        column_values(ids: ["date4"]) {
          id 
          value
          text
          type      
        } 
      }
    	cursor 
    } 
  } 
}

Let me know if this helps!

Best,
Joseph

Well that works partially. I tried query_params as following:

      query_params: 
      {
        rules: [{
          column_id: "date4",
          compare_value: ["EXACT", "2022-11-01", "2023-11-05"], 
          operator: greater_than_or_equals
        }]
      }

But the problem is that it doesn’t respect 2023-11-05 as the upper boundary. You might think how that isn’t a problem because I can write a script that does filtering but that is exactly what I want to avoid. Some boards that we have contain a ton of items and by searching for items that are targeted for certain date we try to reduce number of API calls that we make (i.e. paging).
For now what I did is that I used between operator and min - max dates. It is not ideal but works until API starts supporting filtering by exact dates.

Thanks for your assistance!

1 Like