Problem with JS library adding elements using external array keys

[jakubmarcinowski]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

When calling a select query after using the update option with an external key, the limit does not work correctly. An error "A 'limit' was applied without an explicit 'order'" is returned. After adding an additional order, the limit is not applied at all. The problem does not occur when using select alone.

Example of implementation:

supabase
      .from('home')
      .update(home)
      .eq('id', home.id)
      .select('id, image(url)')
      .limit(1, { foreignTable: 'image' }) // FIX-ME: This is not working;

It is a combination of one to many one house can have many images. As a result, we want to get only one instead of a list of all.

To Reproduce

Steps to reproduce the behavior:

1. Create two arrays in which one has a key to the other with an external key using a one-to-many relationship.
2. Creation of any elements
3. Calling update query from library and then select with external key and limit constraint

Expected behavior

The result should be the same as for select called separately.

[steve-chavez]

Can you share the generated HTTP request of the supabase call? (the URI plus headers)

It seems to be related to this feature: https://postgrest.org/en/stable/references/api/tables_views.html#limited-update-delete, which limits the amount of updated rows. But the order should only be required if you limit the root table.

[jakubmarcinowski]

Unfortunately, I don't know how to share queries from your library because I use it on the BED side. If necessary I will transfer to FED and try to get but the case is very simple. To answer your question I don't want to limit how many elements will get the update just the result of select which is executed after the update. I checked and if I execute it as a separate element then it works correctly ie.

Scenario 1:

supabase
.from('home')
.update(home)
.eq('id', home.id)
.select('id, image(url)')
.limit(1, { foreignTable: 'image' })

The result has many "images", the limit does not work and i get an error about "order"

Scenario 2:

supabase
.from('home')
.update(home)
.eq('id', home.id)

supabase
.from('home')
.select('id, image(url)')
.limit(1, { foreignTable: 'image' })

The result is correct images are limited to 1 and there is no error

[nicetomytyuk]

I've resolve the issue by returning a .maybeSingle() without .limit(1)