Improve Query to both Document and Column communication API

[otaviojava]

Currently, we have query support using a fluent API to both select and delete, but it has several limitations, such as evolute complex conditions.

The proposal is to add a builder method in the Query interfaces such as DocumentQuery, ColumnQuery, etc. This way, it will create more complex queries easier without using the fluent API.

Sort sortName = Sort.asc("name");
Sort sortYear = Sort.desc("year");
DocumentCondition isPoliana = DocumentCondition.eq(Document.of("name", "Poliana"));
DocumentCondition isMillennium = DocumentCondition.gte(Document.of("year", 2000));
DocumentCondition condition = DocumentCondition.and(isPoliana, isMillennium);
     
DocumentQuery query = DocumentQuery.builder("column", "column2")
                             .where(condition)
                             .sort(sortName)
                             .sort(sortYear)
                             .limit(3)
                             .skip(2)
                             .build();

Furthermore, it will make it easier to implement the Criteria API.

[otaviojava]

@amoscatelli does it make your life easier?
What do you think?

[aeciojunior]

It would be interesting to be able to add indices in order to optimize the query.

[deniswsrosa]

Not sure regarding adding indexes as they might take hours to get built, but suggesting the index is definitely a good thing.Many nosql databases tend to have a rule-based optimizer instead of a cost-based one. Even on relational, if the statistics are not updated, it might lead to the query planner to pick the wrong index.

[deniswsrosa]

@otaviojava From my experience, most the complex queries have 3 things:

1. Temporary/virtual tables:
   select val1,val2 from table1 as table1, (select * from preferences where xpto='teat' ) as table2 where ...

2. Subselect referencing values from the main select:

Select * from user where user.balance = (select max(balance) from account where type = user.type)

3. Custom user-defined functions:

Select myCustomDatabaseFunction(value1) from table1

[boaglio]

@otaviojava it would be great if this builder could handle lambda expressions like FluentMongo does
=)

[somayaj]

further, intersect and union queries? how will those be handled? is that part of the spec as well for consideration?

[otaviojava]

@deniswsrosa
Thank you, Denis; those samples are super complex!!
For sure, we must have support for it!

I'll keep my eyes on it as the next step.
Thank you :)

[otaviojava]

Hey @somayaj

We don't support it yet; we're making baby-step progress in the specification.
It is the same as @deniswrosa

It is not yet, but we must have support for it.

[otaviojava]

@boaglio, thank you for the suggestion; we'll look into it.

[amoscatelli]

@otaviojava yea, it looks great.

I am going to use this inside DocumentTemplate (mapping layer) to map Criteria API into low level communication layer objects.

Who is going to work on this ?
I am currently stuck and cannot go further without this.

p.s. I could work on this too. Just let me know.

[otaviojava]

@amoscatelli I'll work on this and then open a PR to review.
Let's work together :)

I will send a PR tonight.

[otaviojava]

@amoscatelli I finished the API and the documentation it left the implementation:

#91

[amoscatelli]

@otaviojava the implementation is ready ? do you need help ?

[avbravo]

I find it very suitable. Offering alternatives makes it easier to use.