Supabase column filter and !inner in query

I have some join tables in my supabase database let say "devices" and "locations" defined like this :

devices {
  id : bigint,
  name: text,
  assigned: bool,
  location_id : bigint
}

locations {
  id : bigint,
  name: text,
}

devices {
  id : bigint,
  name: text,
  assigned: bool,
  location_id : bigint
}

locations {
  id : bigint,
  name: text,
}

with a foreign key location_id in devices pointing to locations. This foreign key is defined only is the assigned flag is true. I'm using refine useTable component defined as this :

const {tableProps} = useTable(
resource: "devices",
meta: {
      select:
        "*, locations(id, name)",
    },

const {tableProps} = useTable(
resource: "devices",
meta: {
      select:
        "*, locations(id, name)",
    },

and my page defined like this :

<Table {...tableProps}>
  <Table.Column
    dataIndex="name"
    title={"Name"}
    defaultSortOrder={getDefaultSortOrder("name", sorters)}
    sorter
  />
  <Table.Column
    dataIndex="location_id"
    title={"Location"}
    filteredValue={getDefaultFilter("location_id", filters, "in")}
    render={(_value, record: IDevice, _index) => {
      return <>{record.locations?.name}</>;
    }}
    filterDropdown={(props) => (
      <FilterDropdown
        {...props}
        mapValue={(selectedKeys) => {
          return selectedKeys.map((i) => parseInt(i.toString()));
        }}
      >
        <Select
          style={{ minWidth: 150 }}
          mode="multiple"
          {...locationsSelectProps}
          placeholder={t("list.searchByLocation")}
        />
      </FilterDropdown>
    )}
      />
</Table>

<Table {...tableProps}>
  <Table.Column
    dataIndex="name"
    title={"Name"}
    defaultSortOrder={getDefaultSortOrder("name", sorters)}
    sorter
  />
  <Table.Column
    dataIndex="location_id"
    title={"Location"}
    filteredValue={getDefaultFilter("location_id", filters, "in")}
    render={(_value, record: IDevice, _index) => {
      return <>{record.locations?.name}</>;
    }}
    filterDropdown={(props) => (
      <FilterDropdown
        {...props}
        mapValue={(selectedKeys) => {
          return selectedKeys.map((i) => parseInt(i.toString()));
        }}
      >
        <Select
          style={{ minWidth: 150 }}
          mode="multiple"
          {...locationsSelectProps}
          placeholder={t("list.searchByLocation")}
        />
      </FilterDropdown>
    )}
      />
</Table>

The problem is when I use the location filter, it doesn't filter the device record until I make my useTable select as "*, locations!inner(id, name)" but then if I'm doing that, I won't see anymore devices with no location_id and assigned flag to false. Is there a way to manage that easily or I need to manage filter manually by myself inside the filter property of useTable ?

5 Replies

kapa.ai•3w ago

Hi @quick691. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- You're encountering a common challenge when working with relational data in Supabase with refine. The issue is related to how Supabase handles inner joins versus regular joins when filtering. Understanding the Problem When you use locations!inner(id, name) in your select statement, you're telling Supabase to use an inner join, which only returns records where there's a match in both tables. This is why devices with no location_id or with assigned=false disappear from your results. However, when you use just locations(id, name) (a left join), you get all devices but the filtering on location doesn't work properly. Solutions There are a few approaches you can take: 1. Dynamic Select Based on Filter You can dynamically change your select statement based on whether a location filter is applied:

const [selectValue, setSelectValue] = useState<string>(
  "*, locations(id, name)"
);

const { tableProps, filters } = useTable({
  resource: "devices",
  meta: {
    select: selectValue,
  },
  // other props...
});

useEffect(() => {
  if (filters.find((item) => item.field === "location_id")?.value) {
    setSelectValue("*, locations!inner(id, name)");
  } else {
    setSelectValue("*, locations(id, name)");
  }
}, [filters]);

const [selectValue, setSelectValue] = useState<string>(
  "*, locations(id, name)"
);

const { tableProps, filters } = useTable({
  resource: "devices",
  meta: {
    select: selectValue,
  },
  // other props...
});

useEffect(() => {
  if (filters.find((item) => item.field === "location_id")?.value) {
    setSelectValue("*, locations!inner(id, name)");
  } else {
    setSelectValue("*, locations(id, name)");
  }
}, [filters]);

This approach was suggested by a user who faced a similar issue with many-to-many relationships in this GitHub issue. 2. Use Client-Side Filtering If your dataset isn't too large, you could fetch all devices and perform the location filtering on the client side:

const { tableProps } = useTable({
  resource: "devices",
  meta: {
    select: "*, locations(id, name)",
  },
  filters: {
    mode: "off", // Turn off server-side filtering
  },
});

// Implement custom filtering logic

const { tableProps } = useTable({
  resource: "devices",
  meta: {
    select: "*, locations(id, name)",
  },
  filters: {
    mode: "off", // Turn off server-side filtering
  },
});

// Implement custom filtering logic

3. Use Supabase's Deep Filtering Refine supports deep filtering with Supabase, which might help in your case:

const { tableProps } = useTable({
  resource: "devices",
  filters: {
    initial: [
      // Your filters
    ],
  },
  meta: {
    select: "*, locations!inner(id, name)",
  },
});

const { tableProps } = useTable({
  resource: "devices",
  filters: {
    initial: [
      // Your filters
    ],
  },
  meta: {
    select: "*, locations!inner(id, name)",
  },
});

As mentioned in the Supabase deep filtering documentation, when filtering based on a table from an inner join, you need to use !inner in your select statement. Important Note The Supabase data provider in refine has a known limitation when it comes to complex filtering scenarios like yours. According to this GitHub issue, there are ongoing discussions about improving how filters work with Supabase Realtime. If none of these approaches work for your specific case, you might need to implement a custom solution by extending the Supabase data provider or handling the filtering logic in your application code. Let me know if you need more specific guidance on implementing any of these solutions! ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•3w ago

<@347285614531444756> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

ratty-blushOP•3w ago

@kapa.ai Using "Dynamic Select Based on Filter" works perfectly thanks

kapa.ai•3w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- I'm glad to hear that the dynamic select approach worked perfectly for your case! This solution elegantly handles the challenge of filtering relational data in Supabase while still showing all records when no filter is applied. The technique of switching between regular joins and inner joins based on filter state is particularly useful for Supabase integrations with refine, especially when dealing with nullable foreign keys or conditional relationships like your assigned flag scenario. If you encounter any other challenges with refine and Supabase in the future, feel free to ask. The community has developed several useful patterns for handling complex data relationships as shown in the GitHub discussions on this topic. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•3w ago

<@347285614531444756> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

Supabase column filter and !inner in query

Did you find this page helpful?