How to do top level filtering *only*?

[ewan-escience]

When doing resource embedding, I would like to apply a top level filter, without filtering out the embedded resources. I think this is best explained with an example:

In the example on top level filtering here, it is suggested that in order to get all films with actor Jehanne, you make a request to /films?select=title,actors!inner(first_name,last_name)&actors.first_name=eq.Jehanne. However, this filter out all other actors, which I would still like to see in some cases. How can I get only films which has actor Jehanne, but with the other actors returned as well?

[wolfgangwalther]

I had this use-case repeatedly, too. For now, you will need to add two embeddings: One to use in your result, one to filter on.

It would be good if we had some kind of exists operator or so, which could look into embeddings to do top-level filtering. Now idea how a syntax or query would look like, though.

[ewan-escience]

Thanks for the quick response! I was figuring out what you meant with two embeddings. Apparently, you can embed the same resource twice, as long as you rename one of the copies.

So in the example, this would look like /films?select=title,actors(first_name,last_name),actors_filter:actors!inner(first_name,last_name)&actors_filter.first_name=eq.Jehanne.

This indeed works, so thanks for that! It is a bit wasteful though, but it's not that bad.

Related, in addition to some exists operator, I would also like to have an allMatch operator, e.g. when I only want films where all actors have first name Jehanne.