GroupBy with union of ListSubQuery

[Ghalleb]

Hi,

It seems that I can't user a GroupBy after using union on ListSubQuery.

Hese is my code:

    final SQLTemplates templates = new PostgresTemplates();

    final SGrainview sgrainview = SGrainview.grainview;
    final SMortalityview smortalityview = SMortalityview.mortalityview;

    final JPASQLQuery queryS = new JPASQLQuery(entityManager, templates);

    final ListSubQuery<Object[]> resgrain = new SQLSubQuery().from(sgrainview).list(sgrainview.all());
    final ListSubQuery<Object[]> resmortality = new SQLSubQuery().from(smortalityview).list(smortalityview.all());

    final List<Object[]> resultS = queryS.union(resgrain, resmortality)
            .orderBy(sgrainview.lotid.asc(), sgrainview.daynumber.asc(), sgrainview.lotfloorid.asc()).list();

It's quite strange as I can order.
Do you have a solution fot that?

Another little question if I may...
Is it possible to use the same where clause on 2 different table (in my case sgrainview and smortalityview).

I would be please if I can do something like:

    BooleanExpression where = .....; (without having to specify which table i'll use)

    final ListSubQuery<Object[]> resgrain = new SQLSubQuery().from(sgrainview).where(where).list(sgrainview.all());
    final ListSubQuery<Object[]> resmortality = new SQLSubQuery().from(smortalityview).where(where).list(smortalityview.all());

[timowest]

Do you have a solution fot that?

I will add groupBy and having to Unions.

Is it possible to use the same where clause on 2 different table (in my case sgrainview and smortalityview).

It's possible if you use the same alias, but that's probably not what you are after.

[Ghalleb]

sgrainview and smortalityview are views and already have groupBy clause in their definition.

What I want to do is to group the results of the 2 views.

Something like

final List<Object[]> resultS = queryS.union(resgrain, resmortality)
        .orderBy(sgrainview.lotid.asc(), sgrainview.daynumber.asc(), sgrainview.lotfloorid.asc())
        .groupBy(sgrainview.lotid, sgrainview.daynumber, sgrainview.lotfloorid)
        .list();

For the alias, do you really think it could work? And How would you use it?

[timowest]

You can express now a query like this

List<Object[]> results = query.union(resgrain, resmortality)
    .orderBy(sgrainview.lotid.asc(), sgrainview.daynumber.asc(), sgrainview.lotfloorid.asc())
    .groupBy(sgrainview.lotid, sgrainview.daynumber, sgrainview.lotfloorid)
    .list();

For the alias, do you really think it could work? And How would you use it?

I just realized that for Querydsl SQL the alias instances are strictly bound to the table they belong to. So sharing the where expression is difficult, unless you share also all the top level expressions that are used in the expression.

[Ghalleb]

Thanks Timo,

Where do you store the Snapshots?

For the where, the views (18 in fact) are all the same because when using a UNION, I should have the same columns...
That's why I though I could use a generic where.

If you have a solution, it will be welcome otherwise I'll continue doing something stupid like:

whereGrain = sgrainview.periodid.in(values);
whereMortality = smortalityview.periodid.in(values);

subquerygrain.where(whereGrain);
subquerymortality.where(whereMortality);

[timowest]

Where do you store the Snapshots?

There are no automated snapshots, but I will make a release this weekend.

If you have a solution, it will be welcome otherwise I'll continue doing something stupid like:

sgrainview and smortalityview are of different type?

You could instantiate them with the same alias and then use one expression for the where part like this

Sgrainview sgrainview = new Sgrainview("view");
Smortalityview smortalityview = new Smortalityview("view");
Predicate where = sgrainview.periodid.in(values);    
subquerygrain.where(where);
subquerymortality.where(where);

I didn't try this, but something like this should work.

[timowest]

Released in 2.9.0