error related to Field
i get the below message when trying to filter by a joined table
error message:
{
"code": "PGRST100",
"details": "unexpected "c" expecting "not" or operator (eq, gt, ...)",
"hint": null,
"message": ""failed to parse logic tree ((all_classes.eq.true,event_classes.class_id.eq.8))" (line 1, column 38)"
}
my code:
const { data, isLoading } = useList<{
id: number;
meal_id: number;
meal_category: string;
}>({
resource: "events",
filters: [
{
operator: "or",
value: [
{ field: "all_classes", operator: "eq", value: true },
{ field: "event_classes.class_id", operator: "eq", value: 8 },
],
},
],
pagination: { current: 1, pageSize: 100 },
meta: {
select: "id,meal_id,meal_category,event_classes!inner(class_id)",
},
});
5 Replies
Hi @antably. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:
----------------------
The error you're encountering is related to how filters are being processed with Supabase. The issue is in the way your OR filter is being constructed and sent to the Supabase backend.
The Problem
When using conditional filters (like
As discussed in this GitHub issue, you can override the filter using the
Instead of using the
The issue with nested filters in Supabase was fixed in a later release as mentioned in the GitHub issue comment. Make sure you're using the latest version of
As a temporary solution, you might need to perform some client-side filtering or modify your query to avoid using complex filter structures. If you're specifically working with Supabase Realtime, note that there's an open issue (#6360) about multiple filters not being supported by Supabase Realtime. I recommend checking your Supabase and refine package versions and updating them if necessary, as this issue might have been resolved in newer versions. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------
or) with Supabase, there's a specific way the filters need to be formatted. The error message indicates that the filter syntax being generated is not compatible with what Supabase expects, particularly when trying to filter on a relation field (event_classes.class_id).
This is a known issue with nested filters in Supabase, as mentioned in this GitHub issue where filters nested in or operators weren't correctly mapped to the respective type with the Supabase dataProvider.
Solution
There are a few approaches you can try:
1. Use the meta.realtimeFilter property (if you're using realtime):
As discussed in this GitHub issue, you can override the filter using the
meta.realtimeFilter property.
2. Try a different filter structure:
Instead of using the
or operator at the top level, you might need to structure your filter differently or use a single filter that can accomplish most of what you need.
3. Check if you're using the latest version:
The issue with nested filters in Supabase was fixed in a later release as mentioned in the GitHub issue comment. Make sure you're using the latest version of
@refinedev/supabase.
4. Consider using a workaround:
As a temporary solution, you might need to perform some client-side filtering or modify your query to avoid using complex filter structures. If you're specifically working with Supabase Realtime, note that there's an open issue (#6360) about multiple filters not being supported by Supabase Realtime. I recommend checking your Supabase and refine package versions and updating them if necessary, as this issue might have been resolved in newer versions. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------
<@426148142124236810> Kapa.ai is still learning and improving, please let me know how I did by reacting below.
eastern-cyanOP•6d ago
it works here with no issue at all
{
operator: "or",
value: [
{ field: "phone", operator: "contains", value },
{ field: "email", operator: "contains", value },
{ field: "name", operator: "contains", value },
],
},
why is it not working in the code below
const { data, isLoading } = useList<{
id: number;
meal_id: number;
meal_category: string;
}>({
resource: "events",
filters: [
{
operator: "or",
value: [
{ field: "all_classes", operator: "eq", value: true },
{ field: "event_classes.class_id", operator: "eq", value: 8 },
],
},
],
pagination: { current: 1, pageSize: 100 },
meta: {
select: "id,meal_id,meal_category,event_classes!inner(class_id)",
},
});
@kapa.ai
it works here with no issue at all
{
operator: "or",
value: [
{ field: "phone", operator: "contains", value },
{ field: "email", operator: "contains", value },
{ field: "name", operator: "contains", value },
],
},
why is it not working in the code below
const { data, isLoading } = useList<{
id: number;
meal_id: number;
meal_category: string;
}>({
resource: "events",
filters: [
{
operator: "or",
value: [
{ field: "all_classes", operator: "eq", value: true },
{ field: "event_classes.class_id", operator: "eq", value: 8 },
],
},
],
pagination: { current: 1, pageSize: 100 },
meta: {
select: "id,meal_id,meal_category,event_classes!inner(class_id)",
},
});
Let me check the knowledge base for you again. One moment! :books:
----------------------
The key difference between your two code examples is that the second one is trying to filter on a relation field (
event_classes.class_id), while the first one only uses direct fields on the main table.
When working with Supabase and refine, filtering on relation fields requires special handling. The error message you received indicates that Supabase's PostgreSQL Row Level Security (RLS) parser is having trouble with the syntax of your filter, particularly when combining a regular field (all_classes) with a relation field (event_classes.class_id) in an OR condition.
Why the first example works
In your first example, all fields (phone, email, and name) are likely direct columns on the same table, and the contains operator is properly supported by Supabase for these types of fields.
Why the second example doesn't work
The issue in your second example is related to how Supabase handles filtering on relation fields within OR conditions. As documented in this GitHub issue, there have been problems with nested CrudFilters values with Supabase not being correctly parsed, especially when using operators like contains, containss, startswith, and endswith.
Additionally, another GitHub issue specifically mentions that "queries nested in or filters are not mapped to the respective type with the Supabase dataProvider."
Possible solutions
1. Use a different approach for relation filtering:
Instead of using the relation field in the filter, you might need to use a custom query or a different approach to achieve the same result.
2. Check your refine and Supabase versions:
Make sure you're using the latest versions, as some of these issues have been fixed in newer releases.
3. Try using a foreign key filter instead:
If possible, restructure your query to filter on foreign keys rather than directly on relation fields.
4. Use the Supabase query builder directly:
For complex queries involving relations, you might need to use the Supabase query builder directly through a custom data provider method.
The fundamental issue is that Supabase has limitations in how it handles filtering on relation fields within OR conditions, and refine's data provider might not be correctly translating these complex filters into the format Supabase expects.
I hope this helps explain the difference between your two examples!
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------<@426148142124236810> Kapa.ai is still learning and improving, please let me know how I did by reacting below.