How to use aggregation/group by in subquery

[AntoineDuComptoirDesPharmacies]

Hi everyone, Hi @rob-bygrave,

We are currently trying to use an Aggregation query as in subquery. We write the subquery as the following :

[PseudoCode]

subQuery = DB.find(Model.class))
subQuery.select("product, lastKnownAd");

DB.find(Model.class))
.where()
.in("id", subQuery)
.findList();

Note : lastKnownAd is an @Aggregation column allowing to get the most recent ad_id for a given product.

We use "product" in first place of the select in order for Ebean to generate the correct GROUP BY clause.
However, we do not want the "product" to be in the select clase because a subquery should have only one column to be usable with in condition. Is there a way to tip Ebean about the group by column to use without adding it to the select clause ?

Thanks in advance for your help.
Yours faithfully,
LCDP

[jnehlmeier]

There is https://groups.google.com/g/ebean/c/30NW-wmP5k0 which asks a similar question. Generally it seems there is no way to explicitly ask for a group by clause unless you use raw sql in one way or another. Obviously raw sql strings are always bad in terms of refactors.

Given that Ebean already has a having method maybe it should also have a groupBy method. It is nice that Ebean generates group by when needed even though it makes queries look weird at times. Especially if the aggregate column of an entity is named in away that does not make the aggregation obvious.

The following examples use Ebean QueryBeans but to some extend also apply to Db.find()-like queries.

Given your example something like

var a = QModel.alias();
new QModel()
  .select(a.product, a.lastKnownAd)
  .having()
    .lastKnownAd.gt(1)
  .findList();

requires the reader to know that that lastKnownAd is an aggregate and that Ebean produces a group by automatically. Otherwise what you read does not make sense.

If Ebean would provide a groupBy method I could see Ebean to allow writing that implicit group by explicitly (Ebean would detect that it does not need to generate the group by anymore in that case). So you could do

var a = QModel.alias();
new QModel()
  .select(a.product, a.lastKnownAd)
  .groupBy()
    .product
  .having()
    .lastKnownAd.gt(1)
  .findList();

if desired which makes query understanding easier for people not super familiar with Ebean and more importantly you could do

var a = QModel.alias();
new QModel()
  .select(a.lastKnownAd)
  .groupBy()
    .product
  .findList();

to solve your issue in a type safe manner.

With newest Ebean and if you are fine using experimental API you could also make the aggregate more visible by using

var a = QModel.alias();
new QModel()
  .select(StdOperators.max(a.ad_id))
  .groupBy()
    .product
  .findList();

[AntoineDuComptoirDesPharmacies]

Thank you for this detailed answer !
I agree with you that having Ebean generating the Group By is good as fallback but seems hard to guess for newcomers whose going to search in their IDE the available methods by completion.

To be honest, during multiple year we were thinking that Group By clause was only modifiable through Raw Query until we read the special chapter in documentation saying that adding a non aggregate field in select will generate a "controlled" Group By.

We are not using QTypes in our code base but your proposal seems good and would result in the similar code :

subQuery = DB.find(Model.class))
subQuery.select("lastKnownAd")
    .groupBy('product');

DB.find(Model.class))
.where()
.in("id", subQuery)
.findList();

Yours faithfully,
LCDP