Supabase Join table sort

how do you create a sorter on a joined table with meta select on useTable when using supabase?

16 Replies

Hi @quick691. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- To create a sorter on a joined table with meta select using useTable in Supabase, you can follow the approach discussed in the knowledge sources. Here is a step-by-step guide: 1. Set up the useTable hook: Use the useTable hook from refine, specifying the resource and meta properties. The meta property should include the select clause to specify the fields you want to retrieve, including the joined table fields. 2. Define the sorter: Use the setSorter function to define the sorter. The sort field can be specified using dot notation to indicate the related table field. Here is an example code snippet based on the provided knowledge sources:

const { tableProps, setSorter } = useTable({
  resource: "posts",
  meta: {
    select: "*, categories!inner(title)",
  },
});

// Set the sorter to sort by the title field of the categories table
setSorter([
  {
    field: "categories.title",
    order: "desc",
  },
]);

const { tableProps, setSorter } = useTable({
  resource: "posts",
  meta: {
    select: "*, categories!inner(title)",
  },
});

// Set the sorter to sort by the title field of the categories table
setSorter([
  {
    field: "categories.title",
    order: "desc",
  },
]);

In this example: - The meta property includes a select clause to retrieve the title field from the categories table. - The setSorter function is used to sort the data by the title field of the categories table in descending order. For more details, you can refer to the relevant documentation and GitHub issues: - Deep Filtering (https://refine.dev/docs/data/packages/supabase/#deep-filtering) - Handling many-to-many relationships (https://refine.dev/docs/data/packages/supabase/#select---handling-many-to-many-relationships) - GitHub Issue #2066 (https://github.com/refinedev/refine/issues/2066) This approach ensures that the sorting is correctly applied to the joined table fields using Supabase with refine. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•7mo ago

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

conscious-sapphireOP•7mo ago

@kapa.ai how to use this setSorter mehtod with Table.Column ?

kapa.ai•7mo ago

Let me check the knowledge base for you. One moment! :books: ---------------------- To use the setSorter method with Table.Column in refine, you can follow these steps: 1. Set up the useTable hook: Use the useTable hook from refine, specifying the resource and meta properties. The meta property should include the select clause to specify the fields you want to retrieve, including the joined table fields. 2. Define the sorter: Use the setSorter function to define the sorter. The sort field can be specified using dot notation to indicate the related table field. 3. Configure Table.Column: Use the sorter property of Table.Column to enable sorting on the column. You can also use the defaultSortOrder property to set the initial sort order. Here is an example code snippet based on the provided knowledge sources:

import { HttpError } from "@refinedev/core";
import { List, TagField, useTable, getDefaultSortOrder } from "@refinedev/antd";
import { Table } from "antd";

interface IPost {
  id: number;
  title: string;
  content: string;
  status: "published" | "draft" | "rejected";
}

const PostList: React.FC = () => {
  const { tableProps, sorter, setSorter } = useTable<IPost, HttpError>({
    resource: "posts",
    meta: {
      select: "*, categories!inner(title)",
    },
    sorters: {
      initial: [
        {
          field: "categories.title",
          order: "desc",
        },
      ],
    },
  });
return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column
          dataIndex="id"
          title="ID"
          sorter={{ multiple: 2 }}
          defaultSortOrder={getDefaultSortOrder("id", sorter)}
        />
        <Table.Column
          dataIndex="title"
          title="Title"
          sorter={{ multiple: 1 }}
          defaultSortOrder={getDefaultSortOrder("title", sorter)}
        />
        <Table.Column
          dataIndex="categories.title"
          title="Category Title"
          sorter
          defaultSortOrder={getDefaultSortOrder("categories.title", sorter)}
        />
        <Table.Column dataIndex="content" title="Content" />
        <Table.Column
          dataIndex="status"
          title="Status"
          render={(value: string) => <TagField value={value} />}
        />
      </Table>
    </List>
  );
};

import { HttpError } from "@refinedev/core";
import { List, TagField, useTable, getDefaultSortOrder } from "@refinedev/antd";
import { Table } from "antd";

interface IPost {
  id: number;
  title: string;
  content: string;
  status: "published" | "draft" | "rejected";
}

const PostList: React.FC = () => {
  const { tableProps, sorter, setSorter } = useTable<IPost, HttpError>({
    resource: "posts",
    meta: {
      select: "*, categories!inner(title)",
    },
    sorters: {
      initial: [
        {
          field: "categories.title",
          order: "desc",
        },
      ],
    },
  });
return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column
          dataIndex="id"
          title="ID"
          sorter={{ multiple: 2 }}
          defaultSortOrder={getDefaultSortOrder("id", sorter)}
        />
        <Table.Column
          dataIndex="title"
          title="Title"
          sorter={{ multiple: 1 }}
          defaultSortOrder={getDefaultSortOrder("title", sorter)}
        />
        <Table.Column
          dataIndex="categories.title"
          title="Category Title"
          sorter
          defaultSortOrder={getDefaultSortOrder("categories.title", sorter)}
        />
        <Table.Column dataIndex="content" title="Content" />
        <Table.Column
          dataIndex="status"
          title="Status"
          render={(value: string) => <TagField value={value} />}
        />
      </Table>
    </List>
  );
};

In this example: - The meta property includes a select clause to retrieve the title field from the categories table. - The setSorter function is used to sort the data by the title field of the categories table in descending order. - The Table.Column component is configured with the sorter property to enable sorting on the column, and the defaultSortOrder property is used to set the initial sort order. For more details, you can refer to the relevant documentation: - Sorting with useTable (https://refine.dev/docs/ui-integrations/ant-design/hooks/use-table/#sorting) - setSorters function (https://refine.dev/docs/ui-integrations/ant-design/hooks/use-table/#setsorters) This approach ensures that the sorting is correctly applied to the joined table fields using Supabase with refine. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•7mo ago

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

conscious-sapphireOP•7mo ago

@kapa.ai it seems dataIndex="categories.title" is not working and I should do dataIndex={["categories", "title"]} instead, getDefaultSortOrder("customers.name", sorters) is not working too.

kapa.ai•7mo ago

Let me check the knowledge base for you. One moment! :books: ---------------------- To correctly set up sorting on a joined table field using dataIndex={["categories", "title"]} and ensure getDefaultSortOrder works, you can follow the steps below: 1. Set up the useTable hook: Use the useTable hook from refine, specifying the resource and meta properties. The meta property should include the select clause to specify the fields you want to retrieve, including the joined table fields. 2. Configure Table.Column: Use the sorter property of Table.Column to enable sorting on the column. Use dataIndex={["categories", "title"]} to correctly reference the joined table field. Ensure getDefaultSortOrder is used properly. Here is an example code snippet based on the provided knowledge sources:

import { HttpError } from "@refinedev/core";
import { List, TagField, useTable, getDefaultSortOrder } from "@refinedev/antd";
import { Table } from "antd";

interface IPost {
  id: number;
  title: string;
  content: string;
  status: "published" | "draft" | "rejected";
}

const PostList: React.FC = () => {
  const { tableProps, sorter } = useTable<IPost, HttpError>({
    resource: "posts",
    meta: {
      select: "*, categories(title)",
    },
    sorters: {
      initial: [
        {
          field: "categories.title",
          order: "desc",
        },
      ],
    },
  });
return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column
          dataIndex="id"
          title="ID"
          sorter={{ multiple: 2 }}
          defaultSortOrder={getDefaultSortOrder("id", sorter)}
        />
        <Table.Column
          dataIndex="title"
          title="Title"
          sorter={{ multiple: 1 }}
          defaultSortOrder={getDefaultSortOrder("title", sorter)}
        />
        <Table.Column
          dataIndex={["categories", "title"]}
          title="Category Title"
          sorter
          defaultSortOrder={getDefaultSortOrder("categories.title", sorter)}
        />
        <Table.Column dataIndex="content" title="Content" />
        <Table.Column
          dataIndex="status"
          title="Status"
          render={(value: string) => <TagField value={value} />}
        />
      </Table>
    </List>
  );
};

import { HttpError } from "@refinedev/core";
import { List, TagField, useTable, getDefaultSortOrder } from "@refinedev/antd";
import { Table } from "antd";

interface IPost {
  id: number;
  title: string;
  content: string;
  status: "published" | "draft" | "rejected";
}

const PostList: React.FC = () => {
  const { tableProps, sorter } = useTable<IPost, HttpError>({
    resource: "posts",
    meta: {
      select: "*, categories(title)",
    },
    sorters: {
      initial: [
        {
          field: "categories.title",
          order: "desc",
        },
      ],
    },
  });
return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column
          dataIndex="id"
          title="ID"
          sorter={{ multiple: 2 }}
          defaultSortOrder={getDefaultSortOrder("id", sorter)}
        />
        <Table.Column
          dataIndex="title"
          title="Title"
          sorter={{ multiple: 1 }}
          defaultSortOrder={getDefaultSortOrder("title", sorter)}
        />
        <Table.Column
          dataIndex={["categories", "title"]}
          title="Category Title"
          sorter
          defaultSortOrder={getDefaultSortOrder("categories.title", sorter)}
        />
        <Table.Column dataIndex="content" title="Content" />
        <Table.Column
          dataIndex="status"
          title="Status"
          render={(value: string) => <TagField value={value} />}
        />
      </Table>
    </List>
  );
};

In this example: - The meta property includes a select clause to retrieve the title field from the categories table. - The Table.Column component is configured with dataIndex={["categories", "title"]} to correctly reference the joined table field. - The getDefaultSortOrder function is used to set the initial sort order. For more details, you can refer to the relevant documentation: - Sorting with useTable (https://refine.dev/docs/ui-integrations/ant-design/hooks/use-table/#sorting) - Initial Filter and Sorter (https://refine.dev/docs/ui-integrations/ant-design/hooks/use-table/#initial-filter-and-sorter) This approach ensures that the sorting is correctly applied to the joined table fields using Supabase with refine. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•7mo ago

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

conscious-sapphireOP•7mo ago

@kapa.ai I finally found the solution, you need to implement render method of Table.Column to display value, use categories(title) as dataIndex or parameter of getFefaultSortOrder method.

kapa.ai•7mo ago

Let me check the knowledge base for you. One moment! :books: ---------------------- To correctly implement sorting on a joined table field using Supabase with refine, you need to use the render method of Table.Column to display the value and use categories(title) as the dataIndex or parameter of the getDefaultSortOrder method. Here is how you can do it: 1. Set up the useTable hook: Use the useTable hook from refine, specifying the resource and meta properties. The meta property should include the select clause to specify the fields you want to retrieve, including the joined table fields. 2. Configure Table.Column: Use the sorter property of Table.Column to enable sorting on the column. Use dataIndex={{["categories", "title"]}} to correctly reference the joined table field. Implement the render method to display the value. Here is an example code snippet:

import { HttpError } from "@refinedev/core";
import { List, TagField, useTable, getDefaultSortOrder } from "@refinedev/antd";
import { Table } from "antd";

interface IPost {
  id: number;
  title: string;
  content: string;
  status: "published" | "draft" | "rejected";
}

const PostList: React.FC = () => {
  const { tableProps, sorter } = useTable<IPost, HttpError>({
    resource: "posts",
    meta: {
      select: "*, categories(title)",
    },
    sorters: {
      initial: [
        {
          field: "categories.title",
          order: "desc",
        },
      ],
    },
  });
return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column
          dataIndex="id"
          title="ID"
          sorter={{ multiple: 2 }}
          defaultSortOrder={getDefaultSortOrder("id", sorter)}
        />
        <Table.Column
          dataIndex="title"
          title="Title"
          sorter={{ multiple: 1 }}
          defaultSortOrder={getDefaultSortOrder("title", sorter)}
        />
        <Table.Column
          dataIndex={["categories", "title"]}
          title="Category Title"
          sorter
          defaultSortOrder={getDefaultSortOrder("categories.title", sorter)}
          render={(value) => value}
        />
        <Table.Column dataIndex="content" title="Content" />
        <Table.Column
          dataIndex="status"
          title="Status"
          render={(value: string) => <TagField value={value} />}
        />
      </Table>
    </List>
  );
};

import { HttpError } from "@refinedev/core";
import { List, TagField, useTable, getDefaultSortOrder } from "@refinedev/antd";
import { Table } from "antd";

interface IPost {
  id: number;
  title: string;
  content: string;
  status: "published" | "draft" | "rejected";
}

const PostList: React.FC = () => {
  const { tableProps, sorter } = useTable<IPost, HttpError>({
    resource: "posts",
    meta: {
      select: "*, categories(title)",
    },
    sorters: {
      initial: [
        {
          field: "categories.title",
          order: "desc",
        },
      ],
    },
  });
return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column
          dataIndex="id"
          title="ID"
          sorter={{ multiple: 2 }}
          defaultSortOrder={getDefaultSortOrder("id", sorter)}
        />
        <Table.Column
          dataIndex="title"
          title="Title"
          sorter={{ multiple: 1 }}
          defaultSortOrder={getDefaultSortOrder("title", sorter)}
        />
        <Table.Column
          dataIndex={["categories", "title"]}
          title="Category Title"
          sorter
          defaultSortOrder={getDefaultSortOrder("categories.title", sorter)}
          render={(value) => value}
        />
        <Table.Column dataIndex="content" title="Content" />
        <Table.Column
          dataIndex="status"
          title="Status"
          render={(value: string) => <TagField value={value} />}
        />
      </Table>
    </List>
  );
};

In this example: - The meta property includes a select clause to retrieve the title field from the categories table. - The Table.Column component is configured with dataIndex={["categories", "title"]} to correctly reference the joined table field. - The render method is implemented to display the value of the categories.title field. For more details, you can refer to the relevant documentation: - Sorting with useTable (https://refine.dev/docs/ui-integrations/ant-design/hooks/use-table/#sorting) - Initial Filter and Sorter (https://refine.dev/docs/ui-integrations/ant-design/hooks/use-table/#initial-filter-and-sorter) This approach ensures that the sorting is correctly applied to the joined table fields using Supabase with refine. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•7mo ago

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

kapa.ai•7mo ago

Let me check the knowledge base for you. One moment! :books: ---------------------- To create a sorter on a joined table field using meta select with useTable in Supabase, you can follow the approach below. This solution ensures that sorting is correctly applied to the joined table fields and displayed properly in the table. Here is the working solution:

import { HttpError } from "@refinedev/core";
import { List, TagField, useTable, getDefaultSortOrder } from "@refinedev/antd";
import { Table } from "antd";

interface IPost {
  id: number;
  title: string;
  content: string;
  status: "published" | "draft" | "rejected";
}

const PostList: React.FC = () => {
  const { tableProps, sorter } = useTable<IPost, HttpError>({
    resource: "posts",
    meta: {
      select: "*, categories(title)",
    },
    sorters: {
      initial: [
        {
          field: "categories.title",
          order: "desc",
        },
      ],
    },
  });

  return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column
          dataIndex="id"
          title="ID"
          sorter={{ multiple: 2 }}
          defaultSortOrder={getDefaultSortOrder("id", sorter)}
        />
        <Table.Column
          dataIndex="title"
          title="Title"
          sorter={{ multiple: 1 }}
          defaultSortOrder={getDefaultSortOrder("title", sorter)}
        />
        <Table.Column
          dataIndex={["categories", "title"]}
          title="Category Title"
          sorter
          defaultSortOrder={getDefaultSortOrder("categories.title", sorter)}
          render={(value) => value}
        />
        <Table.Column dataIndex="content" title="Content" />
        <Table.Column
          dataIndex="status"
          title="Status"
          render={(value: string) => <TagField value={value} />}
        />
      </Table>
    </List>
  );
};

import { HttpError } from "@refinedev/core";
import { List, TagField, useTable, getDefaultSortOrder } from "@refinedev/antd";
import { Table } from "antd";

interface IPost {
  id: number;
  title: string;
  content: string;
  status: "published" | "draft" | "rejected";
}

const PostList: React.FC = () => {
  const { tableProps, sorter } = useTable<IPost, HttpError>({
    resource: "posts",
    meta: {
      select: "*, categories(title)",
    },
    sorters: {
      initial: [
        {
          field: "categories.title",
          order: "desc",
        },
      ],
    },
  });

  return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column
          dataIndex="id"
          title="ID"
          sorter={{ multiple: 2 }}
          defaultSortOrder={getDefaultSortOrder("id", sorter)}
        />
        <Table.Column
          dataIndex="title"
          title="Title"
          sorter={{ multiple: 1 }}
          defaultSortOrder={getDefaultSortOrder("title", sorter)}
        />
        <Table.Column
          dataIndex={["categories", "title"]}
          title="Category Title"
          sorter
          defaultSortOrder={getDefaultSortOrder("categories.title", sorter)}
          render={(value) => value}
        />
        <Table.Column dataIndex="content" title="Content" />
        <Table.Column
          dataIndex="status"
          title="Status"
          render={(value: string) => <TagField value={value} />}
        />
      </Table>
    </List>
  );
};

In this example: - The meta property includes a select clause to retrieve the title field from the categories table. - The Table.Column component is configured with dataIndex={["categories", "title"]} to correctly reference the joined table field. - The render method is implemented to display the value of the categories.title field. - The getDefaultSortOrder function is used to set the initial sort order. For more details, you can refer to the relevant documentation: - Sorting with useTable (https://refine.dev/docs/ui-integrations/ant-design/hooks/use-table/#sorting) - Initial Filter and Sorter (https://refine.dev/docs/ui-integrations/ant-design/hooks/use-table/#initial-filter-and-sorter) This approach ensures that the sorting is correctly applied to the joined table fields using Supabase with refine. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•7mo ago

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

conscious-sapphireOP•7mo ago

@kapa.ai please delete your last message, this is the working solution

`jsx
import { HttpError } from "@refinedev/core";
import { List, TagField, useTable, getDefaultSortOrder } from "@refinedev/antd";
import { Table } from "antd";

interface IPost {
  id: number;
  title: string;
  content: string;
  status: "published" | "draft" | "rejected";
}

const PostList: React.FC = () => {
  const { tableProps, sorter } = useTable<IPost, HttpError>({
    resource: "posts",
    meta: {
      select: "*, categories(title)",
    },
    sorters: {
      initial: [
        {
          field: "categories(title)",
          order: "desc",
        },
      ],
    },
  });
return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column
          dataIndex="id"
          title="ID"
          sorter={{ multiple: 2 }}
          defaultSortOrder={getDefaultSortOrder("id", sorter)}
        />
        <Table.Column
          dataIndex="title"
          title="Title"
          sorter={{ multiple: 1 }}
          defaultSortOrder={getDefaultSortOrder("title", sorter)}
        />
        <Table.Column
          dataIndex="categories(title)"
          title="Category Title"
          sorter
          defaultSortOrder={getDefaultSortOrder("categories(title)", sorter)}
          render={(_ record) => record?.categories?.title }
        />
        <Table.Column dataIndex="content" title="Content" />
        <Table.Column
          dataIndex="status"
          title="Status"
          render={(value: string) => <TagField value={value} />}
        />
      </Table>
    </List>
  );
};

kapa.ai•7mo ago

import { HttpError } from "@refinedev/core";
import { List, TagField, useTable, getDefaultSortOrder } from "@refinedev/antd";
import { Table } from "antd";

interface IPost {
  id: number;
  title: string;
  content: string;
  status: "published" | "draft" | "rejected";
}

const PostList: React.FC = () => {
  const { tableProps, sorter } = useTable<IPost, HttpError>({
    resource: "posts",
    meta: {
      select: "*, categories(title)",
    },
    sorters: {
      initial: [
        {
          field: "categories(title)",
          order: "desc",
        },
      ],
    },
  });
return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column
          dataIndex="id"
          title="ID"
          sorter={{ multiple: 2 }}
          defaultSortOrder={getDefaultSortOrder("id", sorter)}
        />
        <Table.Column
          dataIndex="title"
          title="Title"
          sorter={{ multiple: 1 }}
          defaultSortOrder={getDefaultSortOrder("title", sorter)}
        />
        <Table.Column
          dataIndex="categories(title)"
          title="Category Title"
          sorter
          defaultSortOrder={getDefaultSortOrder("categories(title)", sorter)}
          render={(_ record) => record?.categories?.title }
        />
        <Table.Column dataIndex="content" title="Content" />
        <Table.Column
          dataIndex="status"
          title="Status"
          render={(value: string) => <TagField value={value} />}
        />
      </Table>
    </List>
  );
};

import { HttpError } from "@refinedev/core";
import { List, TagField, useTable, getDefaultSortOrder } from "@refinedev/antd";
import { Table } from "antd";

interface IPost {
  id: number;
  title: string;
  content: string;
  status: "published" | "draft" | "rejected";
}

const PostList: React.FC = () => {
  const { tableProps, sorter } = useTable<IPost, HttpError>({
    resource: "posts",
    meta: {
      select: "*, categories(title)",
    },
    sorters: {
      initial: [
        {
          field: "categories(title)",
          order: "desc",
        },
      ],
    },
  });
return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column
          dataIndex="id"
          title="ID"
          sorter={{ multiple: 2 }}
          defaultSortOrder={getDefaultSortOrder("id", sorter)}
        />
        <Table.Column
          dataIndex="title"
          title="Title"
          sorter={{ multiple: 1 }}
          defaultSortOrder={getDefaultSortOrder("title", sorter)}
        />
        <Table.Column
          dataIndex="categories(title)"
          title="Category Title"
          sorter
          defaultSortOrder={getDefaultSortOrder("categories(title)", sorter)}
          render={(_ record) => record?.categories?.title }
        />
        <Table.Column dataIndex="content" title="Content" />
        <Table.Column
          dataIndex="status"
          title="Status"
          render={(value: string) => <TagField value={value} />}
        />
      </Table>
    </List>
  );
};

In this example: - The meta property includes a select clause to retrieve the title field from the categories table. - The Table.Column component is configured with dataIndex="categories(title)" to correctly reference the joined table field. - The render method is implemented to display the value of the categories.title field. - The getDefaultSortOrder function is used to set the initial sort order. For more details, you can refer to the relevant documentation: - Sorting with useTable (https://refine.dev/docs/ui-integrations/ant-design/hooks/use-table/#sorting) - Initial Filter and Sorter (https://refine.dev/docs/ui-integrations/ant-design/hooks/use-table/#initial-filter-and-sorter) This approach ensures that the sorting is correctly applied to the joined table fields using Supabase with refine. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•7mo ago

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

Supabase Join table sort

Did you find this page helpful?