ActiveRecordFilterQueryBuilder can't deal with "conjunction of relations"

[xeger]

Problem Statement

Given a structure like the following:

class Tag < ActiveRecord::Base
end

class Widget < ActiveRecord::Base
  has_many :tags
end

class TagMedia < MediaType
  attribute :name
end

class WidgetMedia < MediaType
  attribute :tags, Praxis::Collection.of(Tag)
end

When I want to filter for "widgets that have tags A and B," the natural inclination is to define a filter on tags for widgets#index, so that in my filter parameter, I can say things like:

tags.name=A&tags.name=B

However, the query builder generates a nonsensical query in this case. It joins tags a single time and adds where({tags: {nam: 'A'}}).where({tags: {name: 'B'}}) clause -- which is to say, it looks for a single row of tags whose name is simultaneously A and B.

The purpose of this issue is to discuss potential solutions to this problem (which we may not necessarily consider a bug, but which is an impediment to anyone who wants to filter by conjunction-of-relations.)

[xeger]

Solution: Query Builder Uses Multiple Joins

It's theoretically possible for the query builder to spot cases like this and, instead of doing a single join, joining the tags table N separate times with a different constraint each time.

I'm not sure of the level of effort required.

[xeger]

Solution: Resource-Level Filters

Since Praxis filters always translate to a SQL query constraint, they are enormously efficient. However, not every interesting filter predicate can be applied in pure SQL.

One option is to expose a second level of filtering which gets applied "in the resource plane," after the DB models are loaded and transformed into resources. Essentially, this filtering would happen either in a Resource getter-proxy method, or it would happen very late in the request lifecycle e.g. during display.

This could become inefficient if used with huge result sets, because the filtering would need to be done in pure Ruby. Likewise, it might be tricky to write the Ruby code (and respect things like the user's chosen filtering operator, the comparison value, etc).

However, it has the advantage of being tremendously powerful; I can filter resource collections by literally any Ruby predicate that I can write. If used with care to handle corner cases that the DB can't handle, this could solve the problem at hand neatly and elegantly, and make Praxis filtering much more powerful.

[danialm]

Another manifestation of this problem is negation. You cannot ask for widgets w/o a specific tag. Naturally, I want to say /widgets?filters=tag.id!=1 but that would result in the widgets that have tags that are not the tag with id 1. The resulting ActiveRecord relation is something like Widget.joins(:tag).where.not(tag: { id: 1}) but I want to ask for is Widget.all - Widget.joins(:tag).where(tag: { id: 1}).

Another issue that we are experiencing is with very complex filter logic. An example would be when you want to count an association and compare the number against an integer.

For all these issues, we have come up with a general solution in our code that partially goes around praxis filtering. We traverse the filter tree down to its leaf nodes. Process each leaf node, and use the filter tree to merge the results. This approach makes one MySQL query for each leaf node, which can have performance implications. However, the benefits outways for us.

1. This bypasses the active record query issue, because we are not generating active record relations for negation or for many association criteria at the same time.
2. it gives us options when it comes to complex filtering logic. At the leaf node, we have the option to hand the "single_filter" to the praxis filtering extension using craft_filter_query(base_query, filters: single_filter) OR we can delegate it back to the domain model to handle. This is very important to us as we are striving to be able to 100% of the filters in the backend.

This is how our solution works:

1. we receive the request in the controller (typically index endpoint)
2. the controller traverses the filter tree into the leaf nodes
3. while at the leaf node, it consults the domain model to see if the domain model wants to handle the single query
4. if the domain model is willing to handle the signel_query, it calls the specific method on the domain model which returns an ActiveRecord::Relation
5. if the domain model is not willing to handle the signel_query, it delegates to praxis filtering extension by calling craft_filter_query which returns an ActiveRecord::Relation
6. it runs the ActiveRecord::Relation from either previous steps and gets the IDs
7. it handles negation, and, and or while combining all the results based on the filter tree

Here is a little visual we made to communicate internally:

We would love your input on our approach first and foremost. We were also hoping to hear if there is any interest in solving the issues in praxis. It could be in any shape from Praxis core functionality, to a configurable path. We are more than happy to contribute in any form.