How to include endDate in filter

@kapa.ai How can I include results that fall on the end date when filtering between two dates?

5 Replies

Hi @amvdxc. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- To include results that fall on the end date when filtering between two dates, you need to use the gte (greater than or equal to) and lte (less than or equal to) operators in your query. This ensures that the filter includes both the start and end dates. Here's an example of how you can structure your query to include results that fall on the end date:

filter = [
  {
    operator: "and",
    value: [
      {
        field: "createdAt",
        operator: "gte",
        value: "2024-06-30T21:00:00.000Z",
      },
      {
        field: "createdAt",
        operator: "lte",
        value: "2024-07-03T21:00:00.000Z",
      },
    ],
  },
];

filter = [
  {
    operator: "and",
    value: [
      {
        field: "createdAt",
        operator: "gte",
        value: "2024-06-30T21:00:00.000Z",
      },
      {
        field: "createdAt",
        operator: "lte",
        value: "2024-07-03T21:00:00.000Z",
      },
    ],
  },
];

In this example, the filter will include all results where the createdAt field is between 2024-06-30T21:00:00.000Z and 2024-07-03T21:00:00.000Z, inclusive of both dates. This approach is confirmed by the GitHub issue where it was noted that the query should use gte and lte operators instead of in to correctly filter date ranges:

The problem is that it should look something like this:
Query string parameters::
select: *
offset: 0
limit: 10
order: created_at.desc
created_at: gte.2024-06-30T21:00:00.000Z
created_at: lte.2024-07-03T21:00:00.000Z
Query string parameters::
select: *
offset: 0
limit: 10
order: created_at.desc
created_at: gte.2024-06-30T21:00:00.000Z
created_at: lte.2024-07-03T21:00:00.000Z

For more details, you can refer to the GitHub issue (https://github.com/refinedev/refine/issues/6119). If you need further assistance, you can check the refine documentation on handling filters (https://refine.dev/docs/advanced-tutorials/data-provider/handling-filters/#combining-filters). ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4mo ago

<@705425032960016475> kapa.ai is still learning and improving, please let us know how it did by reacting below

national-goldOP•4mo ago

@kapa.ai How can I do it when using rangePickerFilterMapper?

kapa.ai•4mo ago

Let me check the knowledge base for you. One moment! :books: ---------------------- To include results that fall on the end date when using rangePickerFilterMapper, you need to ensure that the rangePickerFilterMapper function correctly maps the selected date range to the gte and lte operators. Here's how you can achieve this: 1. Import the necessary modules and functions:

import { getDefaultFilter } from "@refinedev/core";  
import {  
  DateField,  
  FilterDropdown,  
  rangePickerFilterMapper,  
  useTable,  
} from "@refinedev/antd";  
import { Table, DatePicker } from "antd";

import { getDefaultFilter } from "@refinedev/core";  
import {  
  DateField,  
  FilterDropdown,  
  rangePickerFilterMapper,  
  useTable,  
} from "@refinedev/antd";  
import { Table, DatePicker } from "antd";

2. Use the rangePickerFilterMapper in your component:

export const Posts = () => {  
  const { tableProps, filters } = useTable({  
    filters: {  
      initial: [  
        {  
          field: "created_at",  
          value: ["2022-01-01", "2022-01-31"],  
          operator: "between",  
        },  
      ],  
    },  
  });  
  
  return (  
    <Table {...tableProps} rowKey="id">  
      <Table.Column dataIndex="id" title="ID" />  
      <Table.Column dataIndex="title" title="Title" />  
      <Table.Column  
        dataIndex="createdAt"  
        title="Created At"  
        filterDropdown={(props) => (  
          <FilterDropdown  
            {...props}  
            mapValue={(selectedKeys, event) => {  
              return rangePickerFilterMapper(selectedKeys, event);  
            }}  
          >  
            <DatePicker.RangePicker />  
          </FilterDropdown>  
        )}  
        defaultFilteredValue={getDefaultFilter(  
          "created_at",  
          filters,  
          "between",  
        )}  
      />  
    </Table>  
  );  
};

export const Posts = () => {  
  const { tableProps, filters } = useTable({  
    filters: {  
      initial: [  
        {  
          field: "created_at",  
          value: ["2022-01-01", "2022-01-31"],  
          operator: "between",  
        },  
      ],  
    },  
  });  
  
  return (  
    <Table {...tableProps} rowKey="id">  
      <Table.Column dataIndex="id" title="ID" />  
      <Table.Column dataIndex="title" title="Title" />  
      <Table.Column  
        dataIndex="createdAt"  
        title="Created At"  
        filterDropdown={(props) => (  
          <FilterDropdown  
            {...props}  
            mapValue={(selectedKeys, event) => {  
              return rangePickerFilterMapper(selectedKeys, event);  
            }}  
          >  
            <DatePicker.RangePicker />  
          </FilterDropdown>  
        )}  
        defaultFilteredValue={getDefaultFilter(  
          "created_at",  
          filters,  
          "between",  
        )}  
      />  
    </Table>  
  );  
};

3. Ensure the rangePickerFilterMapper function is correctly implemented: The rangePickerFilterMapper function converts the selected date range to the appropriate format. When the event is onChange, it converts the Dayjs objects to ISO 8601 string format, which can then be used with gte and lte operators.

import type {  
  FilterDropdownProps,  
  MapValueEvent,  
} from "@components/table/components";  
import dayjs from "dayjs";  
  
export const rangePickerFilterMapper = (  
  selectedKeys: FilterDropdownProps["selectedKeys"],  
  event: MapValueEvent,  
) => {  
  if (!selectedKeys) {  
    return selectedKeys;  
  }  
  
  if (event === "value") {  
    return selectedKeys.map((key) => {  
      if (typeof key === "string") {  
        return dayjs(key);  
      }  
  
      return key;  
    });  
  }  
  
  if (event === "onChange") {  
    if (selectedKeys.every(dayjs.isDayjs)) {  
      return selectedKeys.map((date: any) => dayjs(date).toISOString());  
    }  
  }  
  
  return selectedKeys;  
};

import type {  
  FilterDropdownProps,  
  MapValueEvent,  
} from "@components/table/components";  
import dayjs from "dayjs";  
  
export const rangePickerFilterMapper = (  
  selectedKeys: FilterDropdownProps["selectedKeys"],  
  event: MapValueEvent,  
) => {  
  if (!selectedKeys) {  
    return selectedKeys;  
  }  
  
  if (event === "value") {  
    return selectedKeys.map((key) => {  
      if (typeof key === "string") {  
        return dayjs(key);  
      }  
  
      return key;  
    });  
  }  
  
  if (event === "onChange") {  
    if (selectedKeys.every(dayjs.isDayjs)) {  
      return selectedKeys.map((date: any) => dayjs(date).toISOString());  
    }  
  }  
  
  return selectedKeys;  
};

By using the rangePickerFilterMapper function, you ensure that the selected date range is correctly mapped to the gte and lte operators, including results that fall on the end date. For more details, you can refer to the refine documentation on rangePickerFilterMapper (https://refine.dev/docs/ui-integrations/ant-design/components/filter-dropdown/#rangepickerfiltermapper). ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4mo ago

<@705425032960016475> kapa.ai is still learning and improving, please let us know how it did by reacting below