How to filter one-to-one relationships by value in the joined table?

[christopheraranda]

How do you filter the results of a query of one table based on the value in a joined table that has a one-to-one relationship?

For example, given the database in the documentation:

GET /films?select=title,technical_specs(camera) HTTP/1.1

how would I select only films that used the Arriflex 35-III camera?

[laurenceisla]

The filter works the same way as other relationships (see Embedded Filters in the docs). In this case, the query would be (%20 is an url encoded empty space):

GET /films?select=title,technical_specs(camera)&technical_specs.camera=eq.Arriflex%2035-III HTTP/1.1

[laurenceisla]

That's strange, it works for me even with the "technical-specs" table name. I'm using curl, and it gives me the result:

$ curl "http://localhost:3000/films?select=title,technical-specs!inner(camera)&technical-specs.camera=eq.Arriflex%2035-III"
[{"title":"A movie","technical-specs":{"camera": "Arriflex 35-III"}}]

Perhaps the %20 is being taken literally (as-is) in the tool you're using? Maybe try writing the space directly eq.Arriflex 35-III.

[christopheraranda]

I'm testing using swagger-ui which seems to be encoding the = for the camera filter from
technical-specs.camera=eq.Arriflex%2035-III
to
technical-specs.camera%3Deq.Arriflex%2035-III
I'm not sure if that would make a difference?

[wolfgangwalther]

I'm not sure if that would make a difference?

Yeah, that's a big difference, imho.

[laurenceisla]

Yeah, that's a big difference, imho.

Ah, yeah, that changes everything. The Swagger output does not allow for custom query parameters right now, so the technical-specs.camera parameter cannot be added to the /films endpoint. It's a limitation for now.

I'll open an issue to keep track of it. This will be addressed in the postgrest-openapi project.

Edit: Opened #3217

[christopheraranda]

@laurenceisla & @wolfgangwalther I see. Thanks so much for your help! I did notice that the spec being produced was Swagger 2.0 and not OpenAPI 3.x. I didn't realize that would make a difference. I'll switch to using curl or some other tool for testing. Thanks again!