[BUG] Supabase data provider returns wrong sorted list for foreign table colmun

[noritsune]

Describe the bug

I tryed to sort by foreign table colmn with Supabase data provider. But the result is not sorted.

Steps To Reproduce

1. Call getList() of Supabase data provider with sort option like below

sorters: [
    {
      field: "categories.title",
      order: "asc",
    },
]

2. Checks whether the results of getList() are sorted by a specified column, but they are not sorted

Expected behavior

The results of getList() are sorted by the specific column of foreign table.

Packages

@refinedev/supabase: 5.9.2

Additional Context

I already found how to fix this bug.
If you assign me this issue, I can fix Supabase data provider refer to the following issue.
supabase/supabase-js#971

[alicanerdurmaz]

Hello @noritsune, thanks for improving Refine. I assigned it to you 👍

[noritsune]

Thank you for assign. I'm working for this issue.

[aliemir]

Hey @noritsune, any updates on this one? We'll be happy to help if you have any issues 🙏

[noritsune]

Hi @aliemir ,

I’ve researched ordering with Supabase SDK and PostgREST, and I found that Supabase relies on PostgREST for ordering. There are two types of ordering available:

• Top-level Ordering:

  • The query parameter looks like this: order=directors(last_name)

• Embedded Ordering:

  • The query parameter looks like this: actors.order=last_name
  • This type of ordering isn’t explicitly named in the documentation, so I’ve referred to it as Embedded Ordering.

However, the current Supabase DataProvider doesn't distinguish between these two ordering methods. It only supports Embedded Ordering at the moment.

To address this, I’m considering adding a meta.isEmbeddedOrdering option to the getList method within the DataProvider. I believe Top-level Ordering should be the default behavior, as it’s more suited for table views—such as in the example of blog posts.

What are your thoughts on this approach?

By the way, I’m unable to work on this issue at the moment. If there’s someone else who can take it on, please feel free to assign them. If not, I’ll plan to work on it in a few months.

[aliemir]

Hey @noritsune sorry for the late response. Thank you for the explanation. I also think that top-level ordering can be the default but it will require a major version update to not break any existing code. As far as I understand, the current implementation results with a mixed use 🤔 If you use the fields in the base table it will use them for the top-level ordering but if you use a dot notation, it will use them as sorters for the reference table.

Maybe instead of isEmbeddedOrdering we can have useEmbeddedOrdering which can be a boolean or string[] which defines the field values of the sorters to decide which is embedded and which is top-level. Do you think this will be a feasible solution? In the future if Refine's core supports additional parameters to be passed along with the filters and sorters, supabase data provider can move to that and deprecate the useEmbeddedOrdering 🤔