Ordering nested tables doesn't work for more than 1 level

[joshnuss]

Bug report

order() only works if joined table is one level deep.

To Reproduce

Run a query with multiple levels of joins:

My schema: boards -> has many lists -> has many cards.
Each table has a position column.

supabase
  .from('boards')
  .select('id, title, lists ( id, title, position, cards ( id, description, position ))') // 2-levels of nesting
  .order('position') // this works
  .order('position', {foreignTable: 'lists'}) // this works
  .order('position', {foreignTable: 'lists.cards'}) // this doesn't work

Expected behavior

Sorting should work on any level of nesting.

Research

PostgREST does support this, the issue is due to quoting of the foreignTable param here:

postgrest-js/src/lib/PostgrestTransformBuilder.ts

Line 48 in 8cf6e00

const key = typeof foreignTable === 'undefined' ? 'order' : `"${foreignTable}".order`

When I remove the quoting locally, the sorting works correctly.

[kiwicopple]

@soedirgo was there any reason you added the quotes here?

[soedirgo]

Seems like a continuation of #131. Same reason for quoting: to handle special characters.

I think we can just assume that 95% of table/column names don't have special characters, and just let users quote the names manually. It's leaky, but should stop issues like this from happening.

Places to update: order, limit, range, cleanFilterArray.

[github-actions]

🎉 This issue has been resolved in version 0.24.3 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀