How to create "like" filters (dynamically) based on CompanyRecord?

[Maverick Skywalker]

Hello together,

is there a way to create "like" filters, based on CompanyRecord like the example below? My issue with it is, it uses only equal (=) commands and not "like". I'm also looking for a solution with greater (>), greater equal (>=), smaller (<) and smaller equal (<=) for different types. Is there may be a way to create a user defined "condition" function, that is capable to access the entities dynamically. To hardcode the "like" conditions, like shown in the documentation, doesn't helps.

Company filter = gson.fromJson(filterAsJSON, Company.class);

CompanyRecord filterRecord = tx.newRecord(COMPANY, filter);

companys = tx.selectFrom(COMPANY)

.where(condition(filterRecord)) 

.fetch();

Best regards

Maverick Skywalker

[Lukas Eder]

Hi Maverick,

Your example uses the "Query By Example" (QBE) API, which mostly translates to equality comparisons. I think those are a reasonable default. Surely, QBE can also work with ranges, but how would such an API be configured? How would it detect what kind of predicate you want to have expressed given only an input record?

Given that we currently don't have this tool, the obvious solution is to write your own QBE API, where you define your own set of rules, possibly encoded in that JSON data structure (e.g. a range would be encoded not as {"employees": 5}, which translates to company.employees = 5, but as {"employees": [5, 10]}, which translates to company.employees between 5 and 10

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9d4a1960-6813-4f61-9eac-28620b70955e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Maverick Skywalker]

Hi Lukas,

but how would such an API be configured? How would it detect what kind of predicate you want to have expressed given only an input record?

The current idea was to keep it as simple as possible and to provide a company object for each condition type ("like", ">", and so on). The subject is to implement a filter mechanism of a table, in each column you have one filter input as String. I'm building everything from scratch, thus I'm free how to implement it.

the obvious solution is to write your own QBE API

I have started to prototype such a function by my own, but I struggle to build the condition based on the sniped below, connected by "and".

public static Condition likeCondition(Record record) {

    Field<?>[] fields = record.fields();

    

    for(int i = 0; i < fields.length; ++i) {

        fields[i].like(record.get(i).toString());

    }

    return null;

}

Do you have any suggestions? ( I can't create a Condition object, thats the problem thus far)

[Lukas Eder]

On Fri, Jun 14, 2019 at 11:59 AM 'Maverick Skywalker' via jOOQ User Group <jooq...@googlegroups.com> wrote:

Hi Lukas,

but how would such an API be configured? How would it detect what kind of predicate you want to have expressed given only an input record?

The current idea was to keep it as simple as possible and to provide a company object for each condition type ("like", ">", and so on). The subject is to implement a filter mechanism of a table, in each column you have one filter input as String. I'm building everything from scratch, thus I'm free how to implement it.

I see, interesting. I'll give this some thoughts. Surely, the default of using "=" can be overridden in a meaningful way that is easy to discover and simple to use. I'll revert back to this discussion once I have a more clear idea.

 

the obvious solution is to write your own QBE API

I have started to prototype such a function by my own, but I struggle to build the condition based on the sniped below, connected by "and".

public static Condition likeCondition(Record record) {

    Field<?>[] fields = record.fields();

    

    for(int i = 0; i < fields.length; ++i) {

        fields[i].like(record.get(i).toString());

    }

    return null;

}

Do you have any suggestions? ( I can't create a Condition object, thats the problem thus far)

You can append conditions like this, in the loop:

Condition condition = DSL.noCondition(); // Or DSL.trueCondition()

for (...)

    condition = condition.and(...);

Alternatively, you can Stream.reduce a set of conditions using Condition::and, or use DSL.and(Condition...) or DSL.and(Collection<? extends Condition)

I hope this helps,

Lukas

[Maverick Skywalker]

Thanks Lukas that helped a lot. :)

Does provide JOOQ also a method to prevent SQL injections at this point? "record.get(x).toString()"

public static Condition likeCondition(Record record) {

    Condition condition = DSL.noCondition();

    Field<?>[] fields = record.fields();

    for(int x = 0; x < fields.length; x++) {

        if(record.get(x) != null) {

            condition = condition.and(fields[x].like(record.get(x).toString()+"%"));

        }

    }

    

    return condition;

}

Best regards Maverick

[Lukas Eder]

jOOQ by default always creates bind variables.

I hope this helps

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/e480c662-123f-4d5d-b346-3fd1379f6487%40googlegroups.com.

[Maverick Skywalker]

Hi Lukas,

I really thought I could do the other methods completly by my own, but the reallity has proven, that I was wrong about that. I tried the following snippets, where I thought that it was promissing and many other things.

condition = condition.and(fields[x].greaterOrEqual(record.get(x));

or

condition = condition.and(fields[x].greaterOrEqual(DSL.zero()));

or

Integer b = new Integer(5);
condition = condition.and(fields[x].greaterOrEqual(b));

Snippet three should work at least, accourding to you post https://stackoverflow.com/questions/35477640/jooq-empty-condition

Error message "no suitable method found", my project is currently based on jooq 3.11.5.

[Lukas Eder]

Hi Maverick,

This is because of how Java's generics work. Your fields[x] expression is a Field<?> using a wildcard. Unfortunately, there's no way in Java to capture this wildcard in a way for it to be made reusable within a method. You'd have to use an auxiliary private method to work around this limitation.

However, you can resort to workarounds. For example, you can cast your right hand side of an expression to a raw type (Field), which is probably acceptable given that you're writing generic framework logic, and that you're doing this only once. For example:

condition = condition.and(fields[x].greaterOrEqual((Field) record.get(x));

or

condition = condition.and(fields[x].greaterOrEqual((Field) DSL.zero()));

or

Integer b = new Integer(5);

condition = condition.and(fields[x].greaterOrEqual((Field) DSL.val(b)));

I hope this helps,

Lukas

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/6c0bbc52-8033-4c63-be85-fa5be6b02f53%40googlegroups.com.

[Lukas Eder]

On Friday, June 14, 2019 at 1:08:12 PM UTC+2, Lukas Eder wrote:

The current idea was to keep it as simple as possible and to provide a company object for each condition type ("like", ">", and so on). The subject is to implement a filter mechanism of a table, in each column you have one filter input as String. I'm building everything from scratch, thus I'm free how to implement it.

I see, interesting. I'll give this some thoughts. Surely, the default of using "=" can be overridden in a meaningful way that is easy to discover and simple to use. I'll revert back to this discussion once I have a more clear idea.

Giving this more thought, I conclude (again) that QBE is a treacherous paradigm. It seems to offera quick win and we're tempted as ever so often to treat it like a one-size-fits-all solution. However, we already have the one-size-fits-all solution: SQL. We can combine arbitrary fields, values, and operators using the jOOQ API to construct dynamic SQL statements, which is exactly what you're doing right now. You translate your own JSON based query language to SQL using jOOQ, which is great.

Having extended QBE APIs akin to DSL.condition() is not going to work, though, as we would also have to build some sort of translation tool where we can decide based on arbitrary input whether to generate equality, non equality, or entirely different kinds of predicates from a record. A record, however, is not a good starting point for such decisions, as it can only really accommodate COL = VALUE style predicates.

So, I prefer to currently not go any further in this QBE paradigm. For example, I find what Spring Data is doing not a viable path in an API that already supports the entirety of the SQL language (Spring Data does not, so QBE is more viable there): https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example.matchers