foreign-sapphire•10mo ago

Filtering with between in Supabase

I'm trying to useList while filtering a date column. The format of the date in this column is YYYY-MM-DD. When trying to filter using the "between" operator, I get the following error from Supabase:

authProvider.ts:186 Error: Operator between is not supported

authProvider.ts:186 Error: Operator between is not supported

Here's my code:

const [quarterStart, setQuarterStart] = useState('');
const [quarterEnd, setQuarterEnd] = useState('');
// Calculate start and end dates of the current quarter
useEffect(() => {
    const startOfMonth = dayjs().startOf('quarter').format('YYYY-MM-DD');
    const endOfMonth = dayjs().endOf('quarter').format('YYYY-MM-DD');
    setQuarterStart(startOfMonth);
    setQuarterEnd(endOfMonth);
}, []);

const { data, isLoading: brokerageReportsIsLoading } = useList({
    resource: 'brokerage_reports',
    filters: [
        {
            field: 'reportDate',
            operator: 'between',
            value: [quarterStart, quarterEnd], // If today is April 1st 2024, this will return all reports from Q1 2024 (January 1st 2024 - March 31st 2024)
        },
    ],
});
const brokerage_reports_last_q = data?.data ?? [];
console.log(brokerage_reports_last_q);

const [quarterStart, setQuarterStart] = useState('');
const [quarterEnd, setQuarterEnd] = useState('');
// Calculate start and end dates of the current quarter
useEffect(() => {
    const startOfMonth = dayjs().startOf('quarter').format('YYYY-MM-DD');
    const endOfMonth = dayjs().endOf('quarter').format('YYYY-MM-DD');
    setQuarterStart(startOfMonth);
    setQuarterEnd(endOfMonth);
}, []);

const { data, isLoading: brokerageReportsIsLoading } = useList({
    resource: 'brokerage_reports',
    filters: [
        {
            field: 'reportDate',
            operator: 'between',
            value: [quarterStart, quarterEnd], // If today is April 1st 2024, this will return all reports from Q1 2024 (January 1st 2024 - March 31st 2024)
        },
    ],
});
const brokerage_reports_last_q = data?.data ?? [];
console.log(brokerage_reports_last_q);

17 Replies

kapa.ai•10mo ago

Hi @James. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- Based on the information provided in the extracts, it seems that the "between" operator is not supported by the Supabase data provider in Refine. The error message you're receiving from Supabase indicates that the "between" operator is not supported. In the Refine documentation (https://refine.dev/docs/advanced-tutorials/data-provider/handling-filters#handle-filters-in-a-data-provider), it is mentioned that data providers that support or and and filtering logic are NestJS CRUD, Strapi, Strapi v4, Supabase, and Hasura. However, it does not specifically mention the support for the "between" operator. As a workaround, you might need to use two separate filters with "gte" (greater than or equal to) and "lte" (less than or equal to) operators to achieve the same result as "between". Here's an example:

const { data, isLoading: brokerageReportsIsLoading } = useList({
    resource: 'brokerage_reports',
    filters: [
        {
            field: 'reportDate',
            operator: 'gte',
            value: quarterStart,
        },
        {
            field: 'reportDate',
            operator: 'lte',
            value: quarterEnd,
        },
    ],
});

const { data, isLoading: brokerageReportsIsLoading } = useList({
    resource: 'brokerage_reports',
    filters: [
        {
            field: 'reportDate',
            operator: 'gte',
            value: quarterStart,
        },
        {
            field: 'reportDate',
            operator: 'lte',
            value: quarterEnd,
        },
    ],
});

This will return all reports where the reportDate is greater than or equal to quarterStart and less than or equal to quarterEnd, effectively the same as a "between" operation. Please note that this is a workaround and may not be the most efficient solution if you have a large dataset. It's always best to check the official documentation or reach out to the support for the specific data provider you're using for the most accurate information. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•10mo ago

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

foreign-sapphireOP•10mo ago

@kapa.ai Perfect, that works.

kapa.ai•10mo ago

Let me check the knowledge base for you. One moment! :books: ---------------------- I'm glad to hear that the solution worked for you! If you have any more questions or run into any other issues, feel free to ask. Happy coding! ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•10mo ago

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

foreign-sapphireOP•9mo ago

@kapa.ai Related to this, I'm trying to use an "OR" operator with the AntDesign component "useSimpleList". I'm trying to make use of a permanent filter but it's not working as expected.

tsx 
const { listProps } = useSimpleList<INote>({
resource: 'notes',
syncWithLocation: false,
filters: {
permanent: [
{
    field: 'user_id',
    operator: 'eq',
    value: refId,
},
{
    field: 'firm_id',
    operator: 'eq',
    value: refId,
},
],
},
});

tsx 
const { listProps } = useSimpleList<INote>({
resource: 'notes',
syncWithLocation: false,
filters: {
permanent: [
{
    field: 'user_id',
    operator: 'eq',
    value: refId,
},
{
    field: 'firm_id',
    operator: 'eq',
    value: refId,
},
],
},
});

If I remove one filter, it shows results but both I get nothing. I want to show results if refID is equal to user_id OR firm_id.

kapa.ai•9mo ago

Let me check the knowledge base for you. One moment! :books: ---------------------- Based on the provided extracts, it seems that the useSimpleList hook in Refine applies filters in an "AND" logic by default, meaning all conditions must be met. This is why you're not getting any results when you apply both filters; it's looking for records where refId is equal to both user_id and firm_id, which might not exist. Unfortunately, the provided extracts do not mention support for "OR" logic in filters for the useSimpleList hook. However, according to the Refine documentation (https://refine.dev/docs/advanced-tutorials/data-provider/handling-filters#handle-filters-in-a-data-provider), some data providers like NestJS CRUD, Strapi, Strapi v4, Supabase, and Hasura do support or and and filtering logic. If your data provider supports "OR" logic, you might need to adjust your filter structure to use it. If it doesn't, you might need to make two separate requests with useSimpleList and then merge the results. Please note that this is a general suggestion and the actual implementation might vary based on the specific data provider you're using. It's always best to check the official documentation or reach out to the support for the specific data provider you're using for the most accurate information. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•9mo ago

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

foreign-sapphireOP•9mo ago

@kapa.ai So I am using Supabase as the data provider, can you provide an example on how I might be able to update my code?

kapa.ai•9mo ago

Let me check the knowledge base for you. One moment! :books: ---------------------- Based on the information provided in the extracts, it seems that Supabase does support or and and filtering logic. However, the extracts do not provide a specific example of how to use the "OR" operator with the useSimpleList hook in Refine. The general approach would be to structure your filters in a way that applies the "OR" logic. However, the exact syntax and structure might vary based on the specific implementation of the Supabase data provider. Here's a general example of how you might structure your filters to use "OR" logic:

const { listProps } = useSimpleList<INote>({
    resource: 'notes',
    syncWithLocation: false,
    filters: {
        permanent: {
            or: [
                {
                    field: 'user_id',
                    operator: 'eq',
                    value: refId,
                },
                {
                    field: 'firm_id',
                    operator: 'eq',
                    value: refId,
                },
            ],
        },
    },
});

const { listProps } = useSimpleList<INote>({
    resource: 'notes',
    syncWithLocation: false,
    filters: {
        permanent: {
            or: [
                {
                    field: 'user_id',
                    operator: 'eq',
                    value: refId,
                },
                {
                    field: 'firm_id',
                    operator: 'eq',
                    value: refId,
                },
            ],
        },
    },
});

In this example, the or property is an array of filter objects. This should return records where refId is equal to user_id OR firm_id. Please note that this is a general example and the actual implementation might vary. It's always best to check the official documentation or reach out to the support for the specific data provider you're using for the most accurate information. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•9mo ago

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

foreign-sapphireOP•9mo ago

@kapa.ai I get the following error:
Object literal may only specify known properties, and 'or' does not exist in type 'CrudFilter[]'.ts(2353) (property) or: { field: string; operator: string; value: UUID; }[]

kapa.ai•9mo ago

Let me check the knowledge base for you. One moment! :books: ---------------------- Based on the error message, it seems that the or property is not recognized as a valid property in the CrudFilter type. This suggests that the CrudFilter type does not support the or operator directly. However, according to the Github issue (https://github.com/refinedev/refine/issues/5230) in the extracts, Refine supports or and and operators through a ConditionalFilter type. The CrudFilter type is a union of LogicalFilter and ConditionalFilter. Here's how you might structure your filters to use the "OR" logic:

const { listProps } = useSimpleList<INote>({
    resource: 'notes',
    syncWithLocation: false,
    filters: {
        permanent: [
            {
                operator: 'or',
                value: [
                    {
                        field: 'user_id',
                        operator: 'eq',
                        value: refId,
                    },
                    {
                        field: 'firm_id',
                        operator: 'eq',
                        value: refId,
                    },
                ],
            },
        ],
    },
});

const { listProps } = useSimpleList<INote>({
    resource: 'notes',
    syncWithLocation: false,
    filters: {
        permanent: [
            {
                operator: 'or',
                value: [
                    {
                        field: 'user_id',
                        operator: 'eq',
                        value: refId,
                    },
                    {
                        field: 'firm_id',
                        operator: 'eq',
                        value: refId,
                    },
                ],
            },
        ],
    },
});

In this example, the operator property is set to 'or', and the value property is an array of filter objects. This should return records where refId is equal to user_id OR firm_id. Please note that this is a general example and the actual implementation might vary. It's always best to check the official documentation or reach out to the support for the specific data provider you're using for the most accurate information. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•9mo ago

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

foreign-sapphireOP•9mo ago

@kapa.ai Perfect, that seems to have worked.

kapa.ai•9mo ago

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