State and intention of QueryBeans

[jnehlmeier]

In Ebean documentation it says In typical recent applications around 85% of queries were "pure" ORM queries..

I am wondering what typical recent applications means? In my experience I need to use raw nearly everywhere because QueryBeans are only really useful for the simplest queries possible. To me it is more like 15% can be used with "pure" ORM queries and the rest needs to mix-in string based sql. In extreme cases I am writing new QEntity() out of habit just to realize that I can only really do new QEntity().raw().findList(), even though the query itself isn't long and complicated.

I feel like that is very sad because type-safety is very valuable for queries to spot refactoring issues easily.

So I am wondering what is the goal of query beans? Are they so limited on purpose? Were they a nice idea that won't be expanded on? When first sawing them in the docs and used them with very simple queries I really liked them but today they usually give a bad feeling since they are so limited and you constantly loose type-safety, the main goal of QueryBeans.

I have already created two issues to provide two very basic things:

1. Allow subqueries in more situations instead of just in
2. Allow comparison of properties with properties of joined entities

Without these QueryBeans are super limited in its use. I am really thinking about ditching QueryBeans completely just to have the database layer consistently use string based queries.

Have you ever considered generating QueryBean API in a similar way as JOOQ does? JOOQ is way more expressive and IMHO Ebean should reach a very similar level before it can say that it has a type-safe SQL API. Maybe Ebean and JOOQ can be joined together somehow, so that the work JOOQ has already done does not need to be done again for Ebean? I know I can use JOOQ as SQL builder but I don't want to use pure SQL with Ebean since I loose a lot of Ebean functionalities.

[rbygrave]

I am wondering what typical recent applications means?

I did an analysis of the queries of the non-trival applications I had access to at the time. I'd suggest these apps only have one use of "effective dating" / "Type 2 temporal design" so I'd suggest less use of inRangeWith() compared to your app(s).

85% of queries were "pure" ORM queries

My gut says that 85% for me is actually conservative. To some extent that is probably because I designed and maintain query beans so I am quite prepared to enhance and extend them as I see need or common convenience expressions in order to support the "fluid style" and minimise the general weakness around OR/Disjunction.

That is, if I see a case where query beans are "not awesome" I'm typically addressing it.

So I am wondering what is the goal of query beans? Are they so limited on purpose?

Query beans as I see them are "truely awesome" (as long as we can reasonably express the vast majority of our predicates). I give them the "truely awesome" tag because imo they are the simplest possible and easiest to read query construction I have seen (again, as long as we can reasonably express the vast majority of predicates).

They get this simplicity by abstracting over EQL (Ebean query language) which is based on "orm graph paths" and means that we don't explicitly deal with sql joins (for predicates, projection, and breaking queries into multiple sql queries).

created two issues to provide two very basic things

Yes, I see that.

Without these QueryBeans are super limited in its use

Sure. It looks likes the app you are working on uses a lot of effective dating (hence inRangeWith()). It might be good to add some example of the use case and maybe add a failing test to the query bean module to "push the case"? I have only looked briefly at that one.

In terms of the subquery issue, that really looks like a simple case of offering more operators other an IN so likely very easy to action.

JOOQ is way more expressive

JOOQ is targeting SQL and to HAS to be more expressive. Query beans are targeting EQL (Ebean query language) which is a higher abstraction level and orders of magnitude simpler than SQL (and to some extent boils down to "path based predicate/expressions").

[jnehlmeier]

Query beans as I see them are "truely awesome" (as long as we can reasonably express the vast majority of our predicates). I give them the "truely awesome" tag because imo they are the simplest possible and easiest to read query construction I have seen (again, as long as we can reasonably expression the vast majority of predicates).

Yes, they are very readable but priority number one should be type safety. Personally I would generally be fine with "reasonably readable" if it brings full type safety. Well and with JPA's Criteria Builder being the worst readable solution in comparison.

Because Ebean uses JPA annotations and I would guess there are people coming from JPA to Ebean I would expect Ebean's Query Beans to be as expressive as JPQL / Criteria Builder. Otherwise Ebean code might end up being less type-safe if someone really has done full JPA Criteria Builder and now regularly has to fall back to use raw.

JOOQ is targeting SQL and to HAS to be more expressive. Query beans are targeting EQL (Ebean query language) which is a higher abstraction level and orders of magnitude simpler than SQL (and to some extent boils down to "path based predicate/expressions").

Hm yes I mixed a few things here. What I wanted to say is that core SQL features need to exist in Query Beans and thus EQL or generally any SQL abstraction layer that does not want to loose too much expressiveness.
Given there is no documentation about EQL I have literally no idea how to write a string based EQL query and what EQL is capable of. I am only using query beans and raw containing SQL. It could very well be that some EQL features are simply not present in Query Beans yet, because Query Beans API is "hand coded".

Maybe EQL already supports:

• sub queries in all locations that JPQL supports
• comparing properties/columns across subqueries or within queries
• WHERE [NOT] EXISTS
• calling standard db functions (COALESCE, SUM, LOWER, ..) and db specific functions. I know @Aggregate but I would rather see aggregation functions in the query definition and not hidden as entity field or view field, e.g. select(StdFunctions.max(a.prop), PgFunctions.date_part(a.prop, 'year), ...) with PgFunctions being maintained by the developer either manually or generated via DB inspection.

I have skimmed through some queries and I guess the majority of use cases are covered by both issues I have created. Some examples that I have found:

• NOT EXISTS (SELECT ... )
• WHERE a.field = (SELECT max(a_sub.field) ... WHERE a.b = a_sub.b) (also without aggregation and also with other comparison operators. Sometimes with field references to parent query, sometimes without)
• HAVING count(id) = (SELECT count(...) ...)
• AND (SELECT count() ..) = <any number>
• SELECT a FROM Entity a JOIN a.b b WHERE a.prop < b.prop AND a.id IN ()
• ORDER BY CASE WHEN (x IS NULL) THEN y ELSE X END DESC
• COALESCE(SUM(a.field), 0)
• LOWER(CONCAT(COALESCE(a.name, ""), ":", a.description)) LIKE LOWER(:param)

I think my major disappointment is that the current code is string based JPQL and during migration to Ebean the plan is to make it type-safe as much as possible since Query beans are much easier to read than JPA criteria builder. JPA criteria builder is only used in a few situations when building queries dynamically. But realizing that I still have to fall back to strings a lot with Ebean caused me starting this discussion. Especially because Query Beans are treated as the preferred way to query data in Ebean.

If nothing helps I guess I can still use JOOQ as SQL builder + Ebean's findNative though. However minimizing dependencies is also valuable.

[rbygrave]

I have skimmed through some queries ... Some examples that I have found

Excellent. With that we can see:

• A simple omission like where not exists select ... Done now
• Some things I think we can add nicely like a.prop < b.prop, and a.field =|<>|<|>|in|not in <subquery> (~ the 2 existing issues we have) Done now
• Some things that likely desire an "Expression Factory" (a bit like our existing Expr but using type safe properties instead of strings) e.g. CASE WHEN (x IS NULL) THEN y ELSE X END
• Some things that could be done both ways as either "Expression Factory style" or "Query beans fluid style". e.g. COALESCE(SUM(a.field), 0) could potentially be query bean fluid style as - field.sum().coalesce(0) or StdFunctions.coalesce(StdFunctions.sum(a.field), 0) and we'd want to static import the methods of StdFunctions.

I'd suggest the next steps are to firstly knock off the obvious and simple cases first and then prototype the more interesting cases and see how far that gets us.

select(StdFunctions.max(a.prop), PgFunctions.date_part(a.prop, 'year), ...) with PgFunctions ...

I'm thinking those are very doable and yes I agree they would provide a lot of value.

I think where we are heading with this part is a new interface type (say QueryProperty) and new overloaded select(QueryProperty...) method with TQProperty implementing QueryProperty and StdFunctions.max(QueryProperty property) returning a QueryProperty (QueryProperty being a new common interface for all type safe things that can be in the projection).

I need to do a couple of things to clear the decks then we can hop into some of this and see how we go.

[rbygrave]

PR with initial look at StdFunctions - https://github.com/ebean-orm/ebean/pull/2881/files

• Adds TQColumn as a marker interface for both TQProperty and "expressions" like max() etc coming from StdFunctions
• Adds StdFunctions with max(..) etc that return TQColumn
• Adds StdExpressions with concat(..) coalesce(..) etc that take TQColumn + args and return io.ebean.Expression

Ponderings:

• Using toString() as the api for TQColumn which is usually a really bad idea, hmmm (didn't want to expose a method on TQProperty yet)
• StdFunctions and StdExpressions could be combined
• StdFunctions, StdExpressions` and TQColumn could be moved to ebean-api
• I expect PgFunctions to live in ebean-platform-postgres

Still Pondering the cases of:

• HAVING count(id) = (SELECT count(...) ...)
• AND (SELECT count() ..) =
• ORDER BY CASE WHEN (x IS NULL) THEN y ELSE X END DESC
  ... but looks like we have all the other cases done or in this PR

We should be adding generic type query examples tests into ebean-querybean and can look to put Postgres specific function test in with ebean-postgis (but unit tests should reasonably cover things in PgFunctions as well)

[rbygrave]

Ok interesting. Note that we can use query beans with docstore only beans but I can understand that it might be good to have some sort of @DoNotGenerateQueryBeansForMe annotation.

I'm pondering marking this new API as @Experimental (we don't have this annotation yet) or @Deprecated(since = "experimental") in order to merge it in for people to try without needing to build ebean themselves. The proviso is that we might need to adjust the API a little bit when we hit more use cases.

That said, I'm pretty happy with the design currently and I think it finds a good compromise in terms of matching types to functions.

Things to look for:

• We might find out we desire to break Temporal into 3 types to represent Time, Date, DateTime distinctly as different types.
• Older time types (java.sql.Timestamp, java.sql.Date etc) do not implement java.time.Temporal. The API would need more overloaded methods to support these older types for eq lt gt etc (these extend java.util.Date as interesting ugly option here)
• For properties that use AttributeConverter we don't know the type for those properties (they get treated as Object / Other)
• Ideally we get more use cases as tests in ebean-querybean

[rbygrave]

Oh, and the last PR picked up a bunch of inconsistencies in query bean property type hierarchy e.g. POffsetDateTime extended PBaseNumber rather than PBaseDate !!??!! Argh!!

[rPraml]

I wonder if something like this is possible <T extends Temporal | Date | Calendar> before(Property<T> prop)
I currently have no IDE - AFAIR only & operator works.

And having a an empty class StdCalendar extends AbstractStdTemporal<Calendar> will not work on static method.
So maybe we need a class like

public class Q {
   public static StdTemporal<Calendar> calendar =  new StdTemporal<>();
  public static StdTemporal<Date> calendar =  new StdTemporal<>() 
} 

So you can use Q.calendar.max(prop)

We may get problems with things that are not in the class path (like joda)

I hope it is clear what I mean.

[jnehlmeier]

@jnehlmeier Do you build Ebean from source? Do you see yourself having time to look at some of this?

Sorry for the late reply. Didn't had much time last weeks. I skimmed through the pull request and some thoughts:

• I am wondering if all those methods in StdOperators that delegate to Expr shouldn't just live in Expr. I mean if you ever want to add an additional operator you have to modify ExpressionFactory, Expr and now also StdOperators. IMHO quite some maintenance annoyance and in your PR you already used Expr and sometimes Expr.factory(). Seems like some shortcuts in Expr are already missing (haven't verified) and the class is out of sync with ExpressionFactory.
• Using add in query beans feels a bit odd while reading the code. But maybe it is just me because I have never used add before in query beans. So I am used to the discoverability of the fluent builder (using auto completion) of query beans. But of course something like add is needed so developers can use expressions with db functions that are not shipped with ebean.

Will try to play a bit with the PR code in the next days.

[rbygrave]

methods in StdOperators that delegate to Expr shouldn't just live in Expr

Not yet. I'm happy for the strongly typed expressions to be clearly separate at this stage rather than overloaded methods and all mixed in with Expr. I could be wrong of course but more keen to keep it clearer to use for users of the API at this stage.

Using add in query beans feels a bit odd

Yes. We are adding to conjunction/AND or disjunction/OR expression lists. We could maybe take a risk and use and(Expression) + or(Expression) to add an expression to the [current] underlying expression list (and maybe check the type of the expression list to make sure it's compatible with the method being used, that is we can only use and(Expression) when the underlying ExpressionList is a conjunction/AND type and otherwise throw an exception [at runtime]. Hmmm.

Will try to play a bit with the PR code in the next days.

Yeah cool. I think the currently known outstanding work in terms of getting an initial version out is to sort out the bug in the querybean generator with validation annotations in currently unsupported places as per Rolands comment.

[rbygrave]

<T extends Temporal | Date | Calendar>

No, it is not possible but ... that is kind of what we are doing in the PR now in that we use Temporal as a sort of "marker type" for Temporal | Date | Calendar | JodaDate.

Q.calendar.max(prop)

max() is not a problem nor any of similar functions. It is only the expressions/operators that take a bind value where we get the issue that we need overloaded methods ... and the only real problem here is that we need more overloaded methods.

For example, with eq()

  public static Expression eq(Property<Temporal> property, Temporal value) ...

  // we need the overloaded method taking a java.util.Date
  public static Expression eq(Property<Temporal> property, java.util.Date value) ...

  // we need the overloaded method taking a java.util.Calendar
  public static Expression eq(Property<Temporal> property, java.util.Calendar value) ...

... but to some extent people really shouldn't be using Calendar, java.util.Date or Joda types anyway. The overloaded methods for these types could be put into a StdLegacyOps or StdJodaOps etc.

[rPraml]

Q.calendar.max(prop)

That was a bad example from me. I shoud call it foo the next time.

Temporal as a sort of "marker type" for Temporal | Date | Calendar | JodaDate

I'm not sure, if we shoudl do that, This would mean, that we can compare Instant with localDate. Maybe we should make it so
I use before as eq is defined for all data types.

  // ensures that types will match
  public static <T extends Temporal> Expression before(Property<T> property, T value) ...

  // we need the overloaded method taking a java.util.Date (but types will not match, maybe they are available only in StdLegacy)
  public static Expression before(Property<? extends Temporal> property, java.util.Date value) ...
  public static Expression before(Property<? extends Temporal> property, java.util.Calendar value) ...

Some good news:

I've checked the performance impact in our application. The compile time (without tests) will rise from ~48s to ~51s and the application gets ~800kb bigger (the whole war file has ~135MB, so this is not dramatically)

unfortunately, the query bean generator produces some strange code if there are validation annotations. I'm not sure, if other annotation processors we use will affect this (lombok), as I could not reproduce the bug in Ebean. Please check if the following will work:

  @ElementCollection
  @Size(max=10)
  List<@Size(max=20) String> paths;

In Ebean I get an NPE

in my app, I have these annotations

	@ElementCollection
	@Column(name = "additional_names", length = 20)
	@Size(max = 5, message = "hat mehr als {max} Elemente.")
	private List<@Size(max = 20) String> weitereNamen = new ArrayList<>();

and the Annotation processor produces this:

import @javax.validation.constraints.Size(max=20);
import @javax.validation.constraints.Size(max=5;
import Elemente.");
import als;
import de.foconis.core.domain.changerequest.IChangeRequest;
// some valid imports removed
import java.lang.String;
import java.util.List;
import java.util.Map;
import mehr;
import message="hat;
import {max};

  // the property looks also wrong
  public PScalar<QPerson, Size(max=5, message="hat mehr als {max} ") List<Size(max=20) String>> weitereNamen;

[rbygrave]

I'm not sure, if we should do that

I agree - I actually pushed another change that got rid of the "extra type parameter" design so the <T> in Query.Property<T> is just the property type now.

This would mean, that we can compare Instant with localDate.

Yes and that was the intention per say in order to be able to compare a LocalDate property with java.sql.Date property and similarly compare properties Timestamp, Instance, OffsetDateTime with each other ... but playing around with it I didn't really like the result and I have ditched that idea and I think it's much better now.

So now it's back to the single specific java type and that means we can't compare a LocalDate property with java.sql.Date property but I think that is ok. It should be reasonably unlikely that people will mix their "date types" like that. There is however a reasonable chance that people use a mix their 'datetime types' with a mixture of java.sql.Timestamp, Instant, OffsetDateTime and ZonedDateTime ... and folks that do that might hit that restriction where they want to compare an Instant property and OffsetDateTime property and can't (or at least it isn't going to be obvious and easy) but I don't think this is too unreasonable.

So I do like where this PR is at now but I don't think we are perfect here in the case with a mixture of java.sql.Timestamp, Instant, OffsetDateTime and ZonedDateTime but I think its ok - I'd be ok merging this if people were keen to try it.

the query bean generator produces some strange code if there are validation annotations

Ah ok. I can hopefully have a look at that later.