Feature Request: Default search parameter

[Brutus5000]

At FAForever are currently dealing with database load problems due to the increasing amount of data over the years and suboptimal queries from outside consumers (since our API is public).

A potential step for this solution would be predefined parameters (static or dynamic).

Here are few business cases to explain why this makes sense:

• When querying the users, by default only query users with active=true
• When querying products from a catalog by default only query products with stock>0
• When querying games, by default only query games created in the last 12 months
• When querying configuration, by default only query configuration where validTo>now

This would reduce the load on unoptimized queries and would also work well together with partitioning of tables. E.g. partition 50 million games by year, using a default parameter all queries would operate on the latest partition by default only.

PS: I know parts of that can be achieved by duplicating the hibernate entity and using @where clause, but I think that is less flexible in terms of querying and more complex for the developer.

Feedback is appreciated. Hints for implementations as well.

[thaingo]

I had similar problem too. Below is what I have been doing:

1. for each of the use-cases, implement a FilterExpressionCheck
2. combine these checks in respective entities' read permissions

This approach works well for me so far.

[aklish]

This is what we do as well.

…

On Sat, Jul 10, 2021 at 8:16 AM Thai Ngo ***@***.***> wrote: I had similar problem too. Below is what I have been doing: 1. for each of the use-cases, implement a FilterExpressionCheck 2. combine these checks in respective entities' read permissions This approach works well for me so far. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#2189 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABVH6QMDTSCB3NJUGXBEPFDTXBB4HANCNFSM5ACJFHXQ> .

[Brutus5000]

How do you make the FilterExpressionCheck not apply if the user provides his own value?

[aklish]

Not ideal, but the method:

public abstract FilterExpression getFilterExpression(Type<?> entityClass, RequestScope requestScope);

takes a RequestScope. If cast to the internal version of this class (https://github.com/yahoo/elide/blob/master/elide-core/src/main/java/com/yahoo/elide/core/RequestScope.java), you can access the EntityProjection which will include the client provided FilterExpression.

You can use this filter expression to determine whether to return an actual expression or just a TruePredicate.

This will only work in JSON-API as GraphQL has a more complex request structure and uses a different RequestScope class.

Do you have any suggestions for how you would like this to work?

[Brutus5000]

From my "consumer" perspective I'd like to have an additional annotation @DefaultFilter that I can put on my column fields/getters which takes an expression string that is evaluated on query runtime (e.g. to have a dynamic date such as now() - 1 year). This default filter would be added to the filter expressions if the field does not appear in the filter expression already.

[aklish]

I can see two ways this could work.

The default filter expression is a templated RSQL expression:

@DefaultFilter("book.title=='foo'")

Or with templates:

@DefaultFilter("book.createdDate=gt='{{dateMath \\"now\\" \\"-1y\\"}}'")

Since we are filtering a collection, I'm not sure we need a context object here.

The other alternative is for @DefaultFilter to be a JPA store annotation that is supplied with a templated JPQL fragment.

Thoughts? I think option 1 might be best.