More extensive interface queries filtering

The problem

Pioneer v2 (https://github.com/Joystream/pioneer) needs to be able to query groups of events (including multiple different types of entities) for the purpose of displaying latest activity and notifications on the platform. (ie. member's latest activity, working group latest activity, member notifications etc.)

Currently for the purpose of grouping together different types of events, the Event interface was created:

interface Event @entity {
  "(network}-{blockNumber}-{indexInBlock}"
  id: ID!

  "Hash of the extrinsic which caused the event to be emitted"
  inExtrinsic: String

  "Blocknumber of the block in which the event was emitted."
  inBlock: Int!

  "Network the block was produced in"
  network: Network!

  "Index of event in block from which it was emitted."
  indexInBlock: Int!
}

type MembershipBoughtEvent implements Event @entity {
    # ...
}

type MemberProfileUpdatedEvent implements Event @entity {
    # ...
}

type MemberAccountsUpdatedEvent implements Event @entity {
    # ...
}

Allowing queries like:

query getLatestMembershipModuleEvents {
  events(
    where: {
      type_in: [MembershipBoughtEvent, MemberProfileUpdatedEvent, MemberAccountsUpdatedEvent]
    },
    limit: 5,
    orderBy: [inBlock_DESC, indexInBlock_DESC]
  ) {
    ... on MembershipBoughtEvent {
      ...MembershipBoughtEventFields
    }
    ... on MemberProfileUpdatedEvent {
      ...MemberProfileUpdatedEventFields
    }
    ... on MemberAccountsUpdatedEvent {
      ...MemberAccountsUpdatedEventFields
    }
  }
}

As described in more detail in https://github.com/Joystream/pioneer/issues/882#issuecomment-866954029, there is however a need to filter those events more specifically by relations like member, workingGroup, proposal or worker, while only some types of events have those relationships available. This is currently not possible and any attempt to workaround this doesn't seem to work.

There are a few additional, important things to consider:

• Some events fall into multiple categories. For example, OpeningFilledEvent could be filtered either by member, worker or workingGroup, so all of those filters should be availabe (which excludes the possibility of splitting Event interface into more specific interfaces)
• Some events, like (again) OpeningFilledEvent, have one-to-many relationships with entities like member and worker, so it should be possible to use a filter like members_some: { id: $memberId } or memberIds_includes: $memberId.

Solution 1 - allow filtering by specific type fields

The most futureproof solution would be to allow referencing interface childrens' (implementers') fields inside the interface query.
I would imagine a syntax like:

query getLatestMembershipEvents($memberId) {
  events(
    where: {
        type_in: [MembershipBoughtEvent, MemberProfileUpdatedEvent, MemberAccountsUpdatedEvent],
        membershipBoughtEvent: { newMemberId: $memberId },
        memberProfileUpdatedEvent: { memberId: $memberId },
        memberAccountsUpdatedEvent: { memberId: $memberId }
    },
    limit: 5,
    orderBy: [inBlock_DESC, indexInBlock_DESC]
  ) {
    ... on MembershipBoughtEvent {
      ...MembershipBoughtEventFields
    }
    ... on MemberProfileUpdatedEvent {
      ...MemberProfileUpdatedEventFields
    }
    ... on MemberAccountsUpdatedEvent {
      ...MemberAccountsUpdatedEventFields
    }
  }
}

Translating to a query:

SELECT events.id FROM (
    SELECT * FROM membership_bought_event ... WHERE new_member_id = :memberId UNION ALL
    SELECT * FROM member_profile_updated_event ... WHERE member_id = :memberId UNION ALL
    SELECT * FROM member_accounts_updated_event ... WHERE member_id = :memberId
) AS events ORDER BY events.inBlock DESC, events.indexInBlock DESC LIMIT 5 

Note that a filter like this would also need to be supported:

where: {
    type_in: [
        OpeningFilledEvent,
        # ...
    ],
    openingFilledEvent: {
        hiredWorkers_some: { id: $workerId }
        # ...
    }
}

Solution 2 - support privitive arrays filtering

Because relationships doesn't yet seem to be supported by interfaces (https://github.com/Joystream/hydra/issues/359),
I considered adding optional ID and [ID] fields to an Event interface just for the purpose of filtering:

interface Event @entity {
    # ...
  "Working group related to the event (if any)"
  optWorkingGroupId: ID

  "Worker(s) related to the event (if any)"
  optWorkerIds: [ID]

  "Membership(s) related to the event (if any)"
  optMemberIds: [ID]

  "Proposal related to the event (if any)"
  optProposalId: ID
}

This would be a bit limiting, but probably enough to support Pioneer v2 at the current stage.

The problem here is that primitive array filtering doesn't seem to be supported either, so I cannot create a query like:

where: {
    type_in: [
        OpeningFilledEvent,
        # ...
    ],
    openingFilledEvent: {
        optWorkerIds_includes: $workerId
        # ...
    }
}

(note: the array field seems to actually be represented as Text field in the database)

Solution 3 - suppport relationships in interface

Another way to solve this would be to add support for relationships (https://github.com/Joystream/hydra/issues/359)
and relationship filtering to interfaces. This would allow, for example, "outsourcing" the EventRelations to another entity:

type EventRelations @entity {
  "Working group related to the event (if any)"
  optWorkingGroup: WorkingGroup

  "Worker(s) related to the event (if any)"
  optWorkers: [Worker] @deriveFrom(field: "inEventRelations")

  "Membership(s) related to the event (if any)"
  optMembers: [Membership] @deriveFrom(field: "inEventRelations")

  "Proposal related to the event (if any)"
  optProposal: Proposal
}

interface Event @entity {
    # ...
    
    "Relations to filter by"
    relations: EventRelations
}

And then possibly construct a query like:

where: {
    type_in: [
        OpeningFilledEvent,
        # ...
    ],
    eventRelations: { optWorkers_some: { id: $workerId } }
}

Start by reviewing the GraphQL interface-query examples and the related Hydra issue #359 and Pioneer issue #882 comment referenced here. Decide which of the three proposed filtering approaches is appropriate, then verify that mixed event types, relation filters, and one-to-many relationships can be queried as required.