Filtering a 2-level hierarchy with contains in ODATA-v4

Question

Assuming the relationship between Race, Team and Car Manufacturer are all many:many. A race can feature many teams, a team can have many car manufacturers, car manufacturers can sponsor many teams and teams can enter many races.

Using odata v-4, how can I select all races featuring cars by specified manufacturers.

If I wanted to select all the races including Team with id 475 and 476 I would form my odata query as

Race$expand=Team($select=id,name)&$filter=((Team/any(c:((c/id eq 475) or (c/id eq 476)))))

But how would I form my URL if I wanted to select all races featuring teams that use car manufacturer with ford or chevy.

IN SQL I would just do:

SELECT *
FROM   race
WHERE  id IN ((SELECT raceid
               FROM   race_team
               WHERE  teamid IN (SELECT teamid
                                 FROM   team_carmanufacturer
                                 WHERE  carid IN (SELECT id
                                                  FROM   carmanufacturer
                                                  WHERE name IN
                                                 ( 'ford', 'chevy' )
                                                 )))) 

race_team, team_carmanufacturer are just the many to many mapping tables in the database.

Answer 1

You could try something like this:

Race?$filter=Team/any(y:y/Manufacturer/any(z:z/name eq 'ford' or z/name eq 'chevy' ))&$expand=Team($expand=Manufacturer)

It'a bit hard to get this correct without an endpoint. Also make sure, your Controller is supporting an expansiondepth of at least 2.

TBH, i dont think this is a very practical way to use over time. Consider using a static endpoint, which operates on you manufacturer brands as parameter.

Cheers