rest - Design of RESTful endpoint for querying based on aggregate data -

let's have 2 resources: person, movie.

persons/123 {id: 123, firstname: "john", lastname:"travolta"}  persons/124 {id: 124, firstname: "uma", lastname: "thurman"}  persons/125 {id: 125, firstname: "bob", lastname: "saget"}  persons/126 {id: 126, firstname: "christopher", lastname: "walken"}  persons/127 {id: 127, firstname: "steve", lastname: "buscemi"}   movies/1 {id: 1, name: "pulp fiction"}  movies/2 {id:2, name: "reservoir dogs"} 

then, relate two, have resource: cast member

get cast-members?movie.name=pulp%20fiction [     {         id: 502,         movie: {id: 1, name: "pulp fiction"}         actor: {id: 123, firstname: "john", lastname:"travolta"},         character: "vincent vega"     },     {         id: 503,         movie: {id: 1, name: "pulp fiction"}         actor: {id: 124, firstname: "uma", lastname: "thurman"},         character: "mia wallace"     },     {         id: 504,         movie: {id: 1, name: "pulp fiction"}         actor: {id: 126, firstname: "christopher", lastname: "walken"},         character: "buddy holly"     },     ... ] 

if want see of movies christopher walken has been in, know can this:

get cast-members?actor.id=125 

what if want see movies both uma thurman , john travolta in cast? endpoint like?

get cast-members?actor.id=124&actor.id=125 

doesn't work.

we return cast members actor.id either 124 or 125?

[     {         id: 587,         movie: {id: 10, name: "kill bill"},          // john's not in movie         actor: {id: 124, firstname: "uma", lastname: "thurman"},                 character: "the bride"     },     {         id: 597,         movie: {id: 11, name: "saturday night fever"},          // uma's not in movie         actor: {id: 123, firstname: "john", lastname:"travolta"},         character: "tony manero"     },     ... ] 

this wouldn't want, because have join movies on client side (which isn't desirable since means have page through lot of data before being able return result).

the sql query this:

select movie castmember actor in (124, 125) group movie having count(distinct actor) = 2 

is there way translate query makes restful sense?

perhaps problem 'cast-members' resource - it's not clear modelling real resource rather kind of 'synthetic' resource. not tables in relational database resources. restful relationships modeled links between resources rather resources themselves.

the core of resource breakdown here you've got

• movie
• person

and they're (perhaps) grouped collections

• movies
• people

let's structure uri space you've got

• /movie
  • a collection of movies
• /movie/{id}
  • an individual movie given id
• /person
  • a collection of people, possibly not actors in movies
• /person/{id}
  • an individual actor given id

if want find movies person in, search movies resource based on 'actor' query. query take multiple values, because makes perfect sense given movie has more 1 actor.

so, if want find films persons ids 1234 , 5678 in query should

get /movie?actor=1234,5678 

now, implement in few different ways depending on use case. perhaps return list of movie uris , you'd have query each 1 individually, doesn't sound great. perhaps, on other hand, return list of full movie documents match (titles, full cast, year, length, synopsis, reviews etc) - lot of data, might add page parameter...

get /movie?actor=1234,5678&page=2&pagesize=10 

perhaps want of details associated each movie - add parameter details make sense you....

get  /movie?actor=1234,5678&details=title,id,cast 

note: far there hasn't been need 'person' resource. however, response document movies query contain links both individual movie uris , person uris...

movies?actor=12345,5678 [  {      movie: {id: 10, uri:"/movies/10", name: "...",              cast: [{id: 12345, uri:"/people/12345", firstname: ... }                     {id: 5678, uri:"/people/5678", firstname:...}                     {...}                    ]      }  },  {      movie: {id: 11, uri: "/movies/11", name: "...",              cast: [{id: 12345, uri:"/people/12345", firstname: ... }                     {id: 5678, uri:"/people/5678", firstname:...}                     {...}                    ]      }  },     ... ]