Expand filtering options for resources_in_roles in /api/events/filter

[ogenstad]

Describe the current behavior

Currently when listing events you can filter them by using a EventFilter object in JSON format. This allows you to search for events based on the event name, resource, related resource.

However the engine is quite opinionated with regards to how the filter is then used to query the database. For example when using related nodes by specifying an EventRelatedFilter we can see that we can specify a list of key-pairs of prefect.resource.role and prefect.resource.id in the resources_in_roles attribute. The resulting query is then using sa.or_ as seen here:

        if self.resources_in_roles:
            filters.append(
                sa.or_(
                    *(
                        sa.and_(
                            db.EventResource.resource_id == resource_id,
                            db.EventResource.resource_role == role,
                        )
                        for resource_id, role in self.resources_in_roles
                    )
                )
            )

Describe the proposed behavior

For my use-case I'd want this to be an sa.and_ instead, and while I don't expect a breaking change here it would be nice to be able to control this behaviour. Perhaps by introducing a resources_in_roles_operation which would default a value indicating sa.or_ but that you could then set to sa.and_.

The goal would be to use the resources_in_roles as a constraint to further filter the events to a smaller set instead of including additional events for each pair of role and resource_id.

Example Use

No response

Additional context

It looks like the /api/automation/filter supports this concept of operator in one location.