Issues with filtering and sorting using supabase-js

[eensander]

Note This issue concerns the branch feature-improve-tables, until it is merged and removed

There are some problems when attempting to sort and filter using the supabase-js client, especially when referencing foreign tables:

• sorting on foreign table appears unstable

  • order changes result, but is incorrect in my tests
  • order ignores the direction (ASC/DESC) in my tests
  • online recommended solution is creating a view (undesired solution)
  • https://github.com/supabase/supabase-flutter/issues/397 (& many more)
  • current approach is splitting the field by dot (.) and using the second term as foreignTable property, but does not adhere to order anymore.

• searching/filtering on non-string (including ENUM's) values is difficult (with LIKE query),

  • seems to work ok on foreign tables when foreign tables are selected using !inner in the select statement.
  • solution in plain postgres is easy: a ::TEXT cast should be sufficient to solve execute the queries (not possible in supabase)
  • online recommended solution is creating a view/computed column (undesired solution)
  • (https://github.com/orgs/supabase/discussions/3836)

[eensander]

With new insight, there's multiple possible solution directions to allow for filtering on multiple columns, where we can tackle both filtering on non-text columns and on foreign table values:

1. Computed columns that concatenate all searched values. (Supabases recommended solution),
   • Source: https://supabase.com/docs/guides/database/full-text-search#search-multiple-columns
   • Pros:
     • We can keep using the supabase-js client in the backend,
     • We can cast enums and others with ::TEXT
   • Cons:
     • Searchable columns have to be defined in SQL and migrations,
     • Unsure at the moment if getting data from foreign tables is difficult using this approach.
2. View Table that directly contains the data that will visible to the end-user,
   • Pros:
     • We can keep using the supabase-js client in the backend,
     • We can cast enums and others with ::TEXT
     • The table contents the end-user sees better corresponds to the table definition in the View,
     • Probably faster response times, since View will be re-rendered on update (right?)
   • Cons:
     • Have to define the view in SQL, however I would prefer this over 1's cons.
3. Direct database-interaction from the backend, using a raw connection or a query-builder,
   • Pros:
     • We keep the business logic and searchable columns in one place: the backend,
     • We can write the queries more specifically and hence do joining and casting in the backend,
   • Cons:
     • Requires implementing the dependency of a good query-builder and loading it in the context for only limited use-cases,