A child association is a one-to-many relationship from the resource you are filtering. For example, an audio event has many verifications (verifications), many taggings (audio_events_tags) and many comments (audio_event_comments).

A parent association is the other direction: many-to-one. For example, an audio event has one audio recording, which has one site. Filtering on parent associations with the dotted notation (sites.name, audio_recordings.recorded_date) is unaffected by this page and is not deprecated.

To filter by child associations, use scopes. Using standard association notation for child associations is deprecated. Filtering by child associations was an unintended consequence for assoication filtering. It is confusing and often produces incorrect results. See also #1076 and the deprecated below.

A scope applies a filter to the child rows of each parent, and tests whether any, none or all of those child rows match it. All conditions inside one scope are tested against the same child row.

{
  "<child association table name>": {
    "<any | none | all>": <filter>
  }
}

For example, audio events with a verification by user 14 that is a skip for tag 5 or 6:

POST /audio_events/filter

{
  "filter": {
    "verifications": {
      "any": {
        "creator_id": { "eq": 14 },
        "confirmed": { "eq": "skip" },
        "tag_id": { "in": [5, 6] }
      }
    }
  }
}

• none is the same as not around any. Use it for "has not been ..." questions; it includes parents with no child rows.

• all follows the usual logic rule: a parent with no child rows has no failing row, so it matches. To require at least one child row as well, combine all with an any scope:

  {
    "and": [
      { "verifications": { "any": {} } },
      { "verifications": { "all": { "confirmed": { "eq": "skip" } } } }
    ]
  }

Writing a scope

• The key is the table name of the child association: the same name used before the dot in the dotted notation (for example verifications in verifications.creator_id). There is no prefix or suffix.
• The scope object contains exactly one operator: any, none or all.
• A scope can go anywhere a field condition can: at the top level of the filter, or inside and, or and not.

The inner filter

• The inner filter is a normal filter. All combiners (and, or, not) and comparison/subset operators work.

• An empty inner filter {} matches every child row:

  • {"verifications": {"any": {}}} matches events with at least one verification.
  • {"verifications": {"none": {}}} matches events with no verifications.
  • {"verifications": {"all": {}}} matches every event, including those with no verifications.
  • The operator is still required: {"verifications": {}} is invalid, as is a non-object inner filter such as {"verifications": {"any": null}}.

• Field names are the child's own fields, without the table prefix. Inside a verifications scope, creator_id means the verification's creator, not the audio event's creator.

• Conditions written side by side in the inner filter are combined with and, as everywhere else in the filter. They must all hold for the same child row.

• The inner filter can use the dotted notation for the child's own parent associations. For example, the tag of a tagging:

  { "audio_events_tags": { "any": { "tags.text": { "eq": "koala" } } } }

What is allowed

• Scopes work only on direct child associations of the resource being filtered. These are rejected:
  • parent associations (use the dotted notation instead),
  • associations reached through another table (for example, tags when filtering audio events: tags are reached through audio_events_tags; use an audio_events_tags scope with tags.text inside it, as above).
• A scope cannot contain another scope.

Breaking a rule returns a 400 Bad Request with a message that explains the problem.

• Each scope is independent. Conditions outside a scope do not narrow the child rows inside it. If you filter audio events by tag and also want verifications only for those tags, repeat the tag condition inside the scope:

  {
    "filter": {
      "and": [
        { "audio_events_tags.tag_id": { "in": [5, 6] } },
        { "verifications": { "none": { "tag_id": { "in": [5, 6] }, "creator_id": { "eq": 14 } } } }
      ]
    }
  }

• Two scopes can match different rows. Two scopes on the same association, combined with and, may each match a different child row. That is useful (for example, "has a correct and has an incorrect"). To require one row to satisfy everything, put all the conditions in one scope.

• Results are parents, not children. A scope decides whether each parent is returned. It does not tell you which child rows matched.

These all use POST /audio_events/filter. User 14 is the current user and tags 5 and 6 are the tags of interest.

Events user 14 has not verified, counting a skip as not verified:

{
  "verifications": {
    "none": {
      "tag_id": { "in": [5, 6] },
      "creator_id": { "eq": 14 },
      "confirmed": { "not_eq": "skip" }
    }
  }
}

Events another user has verified as incorrect:

{
  "verifications": {
    "any": {
      "tag_id": { "in": [5, 6] },
      "creator_id": { "not_eq": 14 },
      "confirmed": { "eq": "incorrect" }
    }
  }
}

Events with no verifications at all:

{ "verifications": { "none": {} } }

Events where every verification is correct, and there is at least one:

{
  "and": [
    { "verifications": { "any": {} } },
    { "verifications": { "all": { "confirmed": { "eq": "correct" } } } }
  ]
}

Filtering a child association with the dotted notation (for example verifications.creator_id) is deprecated and will be removed in a future version. It still works for now, but it is easy to write a request that looks correct and silently returns the wrong results.

Each dotted condition on a child association is checked on its own: "does this parent have some child row matching this one condition?". Nothing ties the conditions together. That causes three problems.

1. Conditions are not applied to the same row.

   {
     "and": [
       { "verifications.creator_id": { "eq": 14 } },
       { "verifications.confirmed": { "eq": "skip" } }
     ]
   }

   This reads as "a verification by user 14 that is a skip". It actually returns an event with a correct from user 14 and a skip from someone else, because each condition is satisfied by a different verification row.

2. Other conditions do not narrow the child rows. Filter events by tags 5 and 6, and add "not verified by user 14". An event that user 14 verified only for tag 9 is excluded, even though user 14 has not verified it for tag 5 or 6.

3. "None" and "all" cannot be expressed. not around a dotted condition negates that one condition only. There is no way to say "no child row matches all of these" or "every child row matches".

4. Multiple child association filters trash performance. Child association filters are expressed as per-row sub-queries. This rapidly degrades performance with three conditions essentially turning each parent row into an O(N^3) operation.

Rewrite each group of dotted conditions on one child association as a single scope.

The first two rows give the same results as before. The third row changes the results: it fixes problem 1, because all the conditions now have to hold on the same verification.