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);
kapa.ai
kapa.ai67d 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
kapa.ai67d ago
<@204362818592440323> kapa.ai is still learning and improving, please let us know how it did by reacting below
extended-salmon
extended-salmon67d ago
@kapa.ai Perfect, that works.
kapa.ai
kapa.ai67d 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
kapa.ai67d ago
<@204362818592440323> kapa.ai is still learning and improving, please let us know how it did by reacting below
extended-salmon
extended-salmon56d 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
kapa.ai56d 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
kapa.ai56d ago
<@204362818592440323> kapa.ai is still learning and improving, please let us know how it did by reacting below
extended-salmon
extended-salmon56d 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
kapa.ai56d 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
kapa.ai56d ago
<@204362818592440323> kapa.ai is still learning and improving, please let us know how it did by reacting below
extended-salmon
extended-salmon56d 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
kapa.ai56d 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
kapa.ai56d ago
<@204362818592440323> kapa.ai is still learning and improving, please let us know how it did by reacting below
extended-salmon
extended-salmon56d ago
@kapa.ai Perfect, that seems to have worked.
kapa.ai
kapa.ai56d 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
kapa.ai56d ago
<@204362818592440323> kapa.ai is still learning and improving, please let us know how it did by reacting below
More Posts
use refetch result in next lineWhen I use refetch functiom from useCustom, the data variable is not updated inmediatly, what can I upload file using antDI need to upload a file with specific format. Exist any hook to do it?uselist paramsHow can I use useList with params?Issues with remix and useGo with remix router`I have the following error: Cannot update a component (`RouterProvider`) while rendering a differenCreating multiple entries with AntD's Create and useForm with SupabaseI have 2 tables: brokerage_accounts and brokerage_reports. When creating a brokerage_reports entry, how to reset modal component state on close?this is in my modal component: ``` interface Props { modalProps: ModalProps formProps: FormPropRefine and Server ComponentsHello, is there any info regarding Refine and server components? I found this blog post: https://refrefine build --sourcemaprefine build --sourcemap misses mapping for src files the custom code and only includes nodemodulesREFINE GRAPHQL MOCK DATABASEHello, i have a question, does refine mock database in graphql resetted?why can't use underline as name?why can't use underline as name? { name: 'temp_products', How to use _ as name but use path - instead of _How to use _ as name but use path - instead of _How do I send with admin ID?``` import { AuthBindings } from '@refinedev/core' import nookies from 'nookies' const mockUsers = sidemenu elements namehow can I put a name differente than the resource name in the menu elementsuseList disableIs it possible to disable useList based on prop valueuseUpdateHow to update a resource using on button clickHow to current page to 1 on apply filtersHere is my getList code getList: async ({ resource, pagination, filters, meta }) => { consRefresh the page when closing the modal``` export const PointsModal = ({ type, point_id, member_id }: PointsModalProps) => { const { Button click not workingHello All, I have a form that suddenly doesn' t work I have been upgrading the project to 4 and am pAccessing data using useoneThis is my .tsx file import { Typography, Spin, Alert } from "antd"; import { useOne } from "@refineresolversis there a resolver sample for refine app crm?