Does ElectricSQL support nested subqueries in the where clause?

[RooTooRD]

I'm trying to use a subquery in the where clause of a shape request like this:

/v1/shape?columns=id,title,description,created_at,updated_at,is_done&offset=-1&replica=default&table=tmp_app_task&where="id" IN (SELECT "item_id" FROM "tmp_app_taskaccess" WHERE "user_id" = 1 AND "access_level" >= 10)

Is this kind of nested subquery supported by ElectricSQL’s shape API? If not, is there a recommended workaround?

[KyleAMathews]

Not yet though this is something we're planning to start work on fairly soon as it's a common request. The common workarounds are to run that subquery yourself before constructing the where clause or denormalize the access data e.g. with triggers so each table has the access information in the same table.

[RooTooRD]

thanks for the response
after more testing, it looks like the server sends a 409 conflict whenever my where clause changes (like when a user gets new permissions). so i'm thinking the right pattern is for my proxy to just catch that 409, and then immediately retry the request without the stale handle to force a full refetch.
just wanted to quickly confirm with you is that the idiomatic way to handle this? or am i missing a simpler approach

[KyleAMathews]

Yup pretty much! Except you'll want the 409 to return all the way to the client as the 409 tells the client that it's going to be replacing its data. If you catch it in the proxy, then the client will just try to incorporate a brand new shape log on top of the old one which will trigger lots of errors.

[RooTooRD]

Thank you for confirming the role of the 409 status. My follow-up question is about how to handle the live query from the client's perspective when its scope becomes stale.

My core problem is this:

A client has an active long-polling request for a shape (e.g., tasks WHERE id IN (1, 2)). The user then performs an action, like creating a new task, which updates their access rights on the server. The correct WHERE clause should now be WHERE id IN (1, 2, 3).
However, the client's existing long-polling request is unaware of this server-side change. It will continue polling with the outdated WHERE clause, and the user will never see their new task until the connection eventually times out and is re-established.
Given this scenario, what is the currently recommended way to force this active long-polling request to refresh? Is there a way to signal to the existing connection that it needs to re-fetch its definition, or is the intended workaround to have the client code manually destroy the old shape subscription and create a new one?
poc -> https://github.com/nuqayah/django-electric

[KyleAMathews]

The trick here is that the client shouldn't create the where clause (generally) — put access control stuff like this entirely in e.g. Caddy there so the where clause is constructed for the client.

This works exactly the same as you'd build an API. The client just says "get me all my tasks /api/tasks" and then the API constructs the actual query.