Exclude unused stages from API response

[richeterre]

The aim of this PR is to clean up the API response for GET /api/v1/contests as follows: The venues field should only contain stages that are actually used in the contest, i.e. that have associated performances from that contest.

[richeterre]

@denisw I'm not sure how to best tackle this. Do I need a subquery when preloading the contest's stages (the association runs contest -> host -> stages), which fetches that contest's performances and checks if any of them is associated with the stage? Or can it be done with some elaborate joins? I've added a failing test to illustrate what I'm trying to achieve here.

[denisw]

@richeterre I think I'd start the easy way and:

1. Fetch the contest with all stages
2. In a separate query, fetch all performances of a contest.
3. Extract all stage ID's from the performances, then filter the contest's stages using that.

One way to improve on this is to only fetch stage ID's in 2. (SELECT DISTINCT stage_id ...) so that you don't have to load all of the unused performance data or duplicate stage ID's from the database.

A second possible improvement is to do 1. after 2., and to somehow enrich the SQL join done by the contest-stage preload by adding a WHERE stages.id in $STAGE_IDS_YOU_RECEIVED_IN_STEP_2. That might not be easy to do, though. You don't lose much by doing the filtering in code.

[richeterre]

I think your approach would work for a single contest, but for the /contests endpoint I need to fetch multiple contests at once. Here is the current query from foundation.ex (order clause omitted):

query = from c in Contest,
  where: c.timetables_public,
  preload: [host: :stages, contest_categories: :category]

So what I'm trying to do is to preload only the stages that are actually used in that contest. Ecto allows custom queries in preloads, but I can't figure out how such a query would look like. Another thing I've tried was to change the above code to something like this:

query = from c in Contest,
  where: c.timetables_public,
  join: h in assoc(c, :host),
  join: s in assoc(h, :stages),
  join: cc in assoc(c, :contest_categories),
  join: p in assoc(cc, :performances),
  where: p.stage_id == s.id,
  preload: [host: {h, stages: s}, contest_categories: {cc, :category}]

but this would lead to some duplicate associations and didn't make the test case pass either 😬