Filtering relationships

[carlbennettnz]

I've found a case in an app I'm working on where I need to be able to filter relationships. My domain model looks like this:

• Members have many networks
• Members have many tags
• Tags have one network

I need to query a member's tags for a given network. I think I could make this work in a hacky way using SQL views. I could have a network_members view which holds a copy of each member for each network they belong to, with an added network field.

How that would work, if it matters

---

CREATE VIEW network_members AS
  SELECT members.*, network_members.network AS network FROM members
  LEFT JOIN network_members ON
    network_members.member = members.id

The tags relationship would then be built with this join:

JOIN member_tags ON
  network_members.id = member_tags.member
  AND network_members.network = member_tags.network

I would then expose the network_members table instead of the members table to the knex adapter.

---

 
This isn't a great solution though, and I don't think you could do something like that with MongoDB. The design described in this JSON API issue would be a lot nicer. Using that API, I could do this:

GET /members?include=tags
GET /members?include=tags&filter[tags]=(network,42)

I also have a similar problem when making updates. I want to be able to PATCH a member with new tags linkage, but limit the change to tags for a given network. I haven't seen a proposal for this, but I might have missed it. That request could maybe look something like this:

PATCH /members/123?filter[tags]=(network,42)

The only work around for this I can think of is implementing a query transform that intercepts the PATCH requests and modifies the linkage to only change the things it's supposed to change. I also don't think that would work with the view-based workaround for the querying limitations above. There should be a better way.

So with that in mind, a few questions:

• Is there any standardisation of JSON:API filtering coming in the near-ish future? It's always seemed like a big whole in the otherwise-quite-thorough spec to me.
• Do you think that linked proposal would be something worth implementing here?
• Is there any workaround you can think of that would work for both reads and writes?

[ethanresnick]

Is there any standardisation of JSON:API filtering coming in the near-ish future? It's always seemed like a big whole in the otherwise-quite-thorough spec to me.

I kind of doubt it, but I'm not sure standardization will be totally necessary once profiles/1.1 are finalized. My guess is that multiple filtering profiles will emerge, and then ideally one will become the de-facto winner, or perhaps the spec will mark one or more as "recommended".

Do you think that linked proposal would be something worth implementing here?

I definitely haven't thought about it very much, but my gut reaction is that that proposal goes a little bit astray when it uses the type of the related resource(s) in the query parameter (whether it's page or filter). That would seem to run into trouble if multiple relationships point to resources of the same type; to fix this, it seems much better to use the relationship path as the scope.

Also, it's a bit unclear whether that proposal is filtering the primary data based on the values in a relationship, or is filtering the related, ?included resources. I think it's supposed to do the latter, which then begs the question as to how the proposal would express the former.

Imo, the status quo is that the spec already has ways to filter, sort, and paginate the primary data (and it's easy to imagine how these could be extended to sort/filter that data based on the contents of to-one relationships, e.g. /articles?sort=author.name or /articles?filter=(author.active,true)).

So, to cover filtering/paginating/sorting the related resources, I think it would make sense for the spec to just use the relationship path as a prefix on filter/page/sort, i.e.

GET /articles
  # sorts primary data, as allowed today
  ?sort=author.name

  # includes authors, with a filter on them.
  &include=author
  &filter[author]=(active,true)

  # includes 3 comments per article, with the linkage
  # sorted by the name of the comment's author.
  # comments is a relationship name that happens to match the type name.
  &sort[comments]=author.name
  &page[comments][size]=3

At the moment, though, the above isn't spec compliant because: 1) the spec reserves the ?sort parameter and doesn't define the relationship path–scoped variant; 2) the spec's current language would seem to imply that ?include=author must include all authors unless there are pagination links in the relationship object.

Still, I think the above would make a pretty good spec proposal.

Note that the spec would probably do something similar with sparse fieldsets; i.e., you'd say that fields[TYPE] applies document-wide to resources of that type, while ?fields[PATH][TYPE] only applies to resources of the given type on the given relationship path (and the list of fields is automatically unioned with the corresponding fields[TYPE] parameter, if any). Also, the spec would probably have to define new parameter names for filtering and paginating (maybe ?select and ?paginate?), since it's already given ?filter and ?sort over to userland, so has lost the ability to add scopes to them, except through a profile.

Until any of this stuff gets into the spec, we could write and implement a profile that offers the same functionality. (It's relatively simple to work around the spec's current constraints; e.g., if related resources brought in by ?include can't compliantly be filtered, we'd just replace include with a parameter like ?includeWhere[PATH]=FILTER_EXPR).

But actually, I'm realizing now that this deep querying stuff (which JSON:API desperately needs) is probably just a long tangent here, as I don't think it's necessarily the best solution to this problem...

PATCH /members/123?filter[tags]=(network,42)

Since you need to do full replacement on just the tags associated with a given network, I think the easiest way to model that in the API is to have a "virtual" relationship -- not serialized by default -- for each network's tags. I.e. every "members" resource would have invisible relationships for tagsNetwork1, tagsNetwork2, ... tagsNetwork42. Then, fetching just the tags for a given network becomes as simple as GET /members?include=tagsNetwork42 and updating becomes PATCH /members/relationships/tagsNetwork42.

Basically, you want to treat all the tags associated with a given network as a distinct set with a referenceable identity, and the natural way json:api supports that is by defining a relationship...

To implement these relationships, I think the simplest way would be to subclass the knex adapter, to materialize the relationship in the Resources it returns if the Query has it in select/populates, and to support replacement on those relationships. I think that would be relatively straightforward?

You could probably get away without subclassing the adapter too by doing some fancy query rewriting using the Postgres rules engine, but that sounds way more bug-prone and harder to maintain.