Union and endSetWith

[wsaca]

Hi, I'm trying to use union but I'm having some problems, maybe a bug or there is something I'm doing wrong.

1. No transactional EntityManager available: When I use union spring give me this error and to resolve it I need to use @Transactional, why should I use it if this is a query?
2. After endSetWith() if I use setMaxResults() or orderBy() this is not working.

This is the query printed with getQueryString():

SELECT root, (SELECT COUNT(*) FROM ApplicationServer applicationServer) AS total, 0L AS hasPrevious, 0L AS hasNext FROM ApplicationServer root WHERE root.applicationId = :param_0
UNION ALL
SELECT root, (SELECT COUNT(*) FROM ApplicationServer applicationServer) AS total, 0L AS hasPrevious, 0L AS hasNext FROM ApplicationServer root WHERE root.applicationId = :param_1

I would like to build something like this (with order by and limit):

SELECT root, (SELECT COUNT(*) FROM ApplicationServer applicationServer) AS total, 0L AS hasPrevious, 0L AS hasNext FROM ApplicationServer root WHERE root.applicationId = :param_0 ORDER BY root.createdAt ASC LIMIT 10
UNION ALL
SELECT root, (SELECT COUNT(*) FROM ApplicationServer applicationServer) AS total, 0L AS hasPrevious, 0L AS hasNext FROM ApplicationServer root WHERE root.applicationId = :param_1 ORDER BY root.createdAt ASC LIMIT 10

I'm using H2 and Oracle.

[wsaca]

Well, I found an alternative using unions in the where clause and some tricks because build dynamic queries is hard, I show an example:

CriteriaBuilder<Tuple> cb = cbf.create(em, Tuple.class)
.from(ApplicationServer.class, "rootFrom")
.select("rootFrom")
.selectSubquery("total")
    .from(ApplicationServer.class)
    .select("COUNT(*)")
    .where("applicationId").eqExpression("rootFrom.applicationId")
    .end()
.orderByDesc("id")
.where("id").in()
        .from(ApplicationServer.class)
        .where("applicationId").eq(1L)
        .select("id")
        .orderByDesc("id")
        .setMaxResults(2)
    .union()
        .from(ApplicationServer.class)
        .where("applicationId").eq(2L)
        .select("id")
        .orderByDesc("id")
        .setMaxResults(2)
    .union()
        .from(ApplicationServer.class)
        .where("applicationId").eq(3L)
        .select("id")
        .orderByDesc("id")
        .setMaxResults(2)
    .endSet()
.end()
;

Now I dont need to use @Transactional, this allows me to use dataloaders with connections in GraphQL and avoid the N+1 problem...

[wsaca]

@beikov I also tried your suggestion, getQueryString() print correctly the query but when getResult() is called nothing happens... Maybe an internal error is not propagated to the application... Now I have a different solution but I will try to replicate this problem with a small example... Thank you...

[beikov]

Interesting. What do you mean nothing happens? Anyway, looking forward to see an example of this issue :)

[wsaca]

@beikov I have created the demo application: demo.zip

I used JDK 11, H2 as database (oracle was also tested) and I added two queries that you could test using:

1. http://localhost:8082/demo/query1: This is not working because the SQL is generated incorrectly:

Generated:

select * from (select role0_.rol_id as col_0_0_, (select count(*) from tal_role role1_ where role1_.application_id=role0_.application_id) as col_1_0_, role0_.rol_id as rol_id1_2_, role0_.application_id as applicat2_2_, role0_.aud_created_at as aud_crea3_2_, role0_.rol_name as rol_name4_2_ from tal_role role0_ where role0_.application_id=? order by role0_.rol_id DESC) 
UNION ALL 
select * from (select role0_.rol_id as col_0_0_, (select count(*) from tal_role role1_ where role1_.application_id=role0_.application_id) as col_1_0_, role0_.rol_id as rol_id1_2_, role0_.application_id as applicat2_2_, role0_.aud_created_at as aud_crea3_2_, role0_.rol_name as rol_name4_2_ from tal_role role0_ where role0_.application_id=? order by role0_.rol_id DESC) limit ?

Expected:

select * from (select role0_.rol_id as col_0_0_, (select count(*) from tal_role role1_ where role1_.application_id=role0_.application_id) as col_1_0_, role0_.rol_id as rol_id1_2_, role0_.application_id as applicat2_2_, role0_.aud_created_at as aud_crea3_2_, role0_.rol_name as rol_name4_2_ from tal_role role0_ where role0_.application_id=? order by role0_.rol_id DESC limit ?) 
UNION ALL 
select * from (select role0_.rol_id as col_0_0_, (select count(*) from tal_role role1_ where role1_.application_id=role0_.application_id) as col_1_0_, role0_.rol_id as rol_id1_2_, role0_.application_id as applicat2_2_, role0_.aud_created_at as aud_crea3_2_, role0_.rol_name as rol_name4_2_ from tal_role role0_ where role0_.application_id=? order by role0_.rol_id DESC limit ?)

2. http://localhost:8082/demo/query2: This is working, but the option 1 had a better performance in my tests.

[beikov]

Thanks a lot! I'll take a look as soon as possible

[beikov]

I'll investigate this further under #1274

For now, I think a possible workaround would be to use Object[] instead of Tuple as query result type.

[beikov]

This was a bug and was fixed as part of #1274

[RaulPampliegaMayoral]

Hi!

So first of all, having to use @Transactional isn't necessarily a problem. A database always runs queries in a transaction, so if you don't explicitly start the transaction, an implicit one will be started at some point. I don't know why Spring complains in this particular case, but if you could share the stack trace and example code, I can look into this.

I think that the query you desire could look like the following:

cbf.startSet(em, Tuple.class)
		.from(ApplicationServer.class, "rootFrom")
		.select("rootFrom")
		.selectSubquery("total")
			.from(ApplicationServer.class)
			.select("COUNT(*)")
			.where("applicationId").eqExpression("rootFrom.applicationId")
		.end()
		.where("applicationId").eq(1L)
		.orderByDesc("id")
        .setMaxResults(10)
	.endSet()
	.unionAll()
		.from(ApplicationServer.class, "rootFrom")
		.select("rootFrom")
		.selectSubquery("total")
			.from(ApplicationServer.class)
			.select("COUNT(*)")
			.where("applicationId").eqExpression("rootFrom.applicationId")
		.end()
		.where("applicationId").eq(2L)
		.orderByDesc("id")
        .setMaxResults(10)
	.endSet()
.end();

Does that work for you?

Hi @beikov I want to make a query like that but dinamically but i'm not having success. I keep getting the following error all the time: Modifications to a query after connecting with a set operation is not allowed!

Is possible to make this query dinamically (subqueries depending on a list)?

Thanks in advance

[RaulPampliegaMayoral]

Hi again @beikov In my current project we are using hibernate 6 and i didn't realized that i can achieve what i want with Hibernate Critera Api:

       var builder = em.unwrap(Session.class).getCriteriaBuilder();
        List<JpaCriteriaQuery<Tuple>> subqueries = new ArrayList<>(searchParams.getSeverities().size());
        for (var severity : searchParams.getSeverities()) {
            var subquery = builder.createTupleQuery();
            Root<AlertJpa> count = subquery.from(AlertJpa.class);
            subquery.multiselect(builder.literal(severity.name()), builder.count(count));
            subquery.where(buildSimplePredicates(builder, count, from, to, searchParams)
                .toArray(new Predicate[0]));
            subqueries.add(subquery);
        }

        var data = em.createQuery(builder.union(subqueries.get(0),
            subqueries.subList(1, subqueries.size()).toArray(new CriteriaQuery[0])))
            .getResultList();

And with this i have one query with differents counts.
Nevertheless I'm courious about how could be done with Blaze Persistence because i have not been able to get it

Thanks agais for your great job and your time

[beikov]

I think the following should do it:

        CriteriaBuilder<Tuple> start = null;
        LeafOngoingSetOperationCriteriaBuilder<Tuple> intermediateBuilder = null;

        for (var severity : searchParams.getSeverities()) {
            BaseCriteriaBuilder<Tuple> cb;
            if ( start == null ) { 
                cb = start = cbf.create(em, Tuple.class);
            } else {
                cb = intermediateBuilder = intermediateBuilder == null ? start.unionAll() : intermediateBuilder.unionAll();
            }
            cb.from(AlertJpa.class, "r")
            .select("count(*)")
            .where("r.id").eq(searchParams.getId())
            .where("r.active").eq(searchParams.getActive());
        }

        var data = intermediateBuilder.endSet().getResultList();

but it's hard to know what you want to do exactly.

[RaulPampliegaMayoral]

@beikov Thanks for your answer and sorry for not having put the sql i was expecting. It would be something like that:

select
(select count(a1_0.id) from platform.alert a1_0 where a1_0.alert_ts between '2013-01-01 00:00:00.0' and '2024-01-22 11:14:24.86345601' and a1_0.tenant_id='12345678-1234-1234-1234-123456789abc' and a1_0.active=true and a1_0.severity in ('CRITICAL')) as critical,
(select count(a1_0.id) from platform.alert a1_0 where a1_0.alert_ts between '2013-01-01 00:00:00.0' and '2024-01-22 11:14:24.86345601' and a1_0.tenant_id='12345678-1234-1234-1234-123456789abc' and a1_0.active=true and a1_0.severity in ('STANDARD')) as standar,
(select count(a1_0.id) from platform.alert a1_0 where a1_0.alert_ts between '2013-01-01 00:00:00.0' and '2024-01-22 11:14:24.86345601' and a1_0.tenant_id='12345678-1234-1234-1234-123456789abc' and a1_0.active=true and a1_0.severity in ('ERROR')) as error

Note: The sql achieved with hibernate criteria api is the following:

select 'critial',count(a1_0.id) 
from platform.alert a1_0
where a1_0.alert_ts between '2013-11-29 00:00:00.0' and '2024-01-22 11:14:24.86345601' 
and a1_0.tenant_id='12345678-1234-1234-1234-123456789abc' 
and a1_0.active=true 
and a1_0.severity in ('CRITICAL') 
union 
select 'error',count(a2_0.id) 
from platform.alert a2_0
where a2_0.alert_ts between '2013-11-29 00:00:00.0' and '2024-01-22 11:14:24.86345601' 
and a2_0.tenant_id='12345678-1234-1234-1234-123456789abc' 
and a2_0.active=true 
and a2_0.severity in ('ERROR') 
union 
select 'standard',count(a2_0.id) 
from platform.alert a2_0
where a2_0.alert_ts between '2013-11-29 00:00:00.0' and '2024-01-22 11:14:24.86345601' 
and a2_0.tenant_id='12345678-1234-1234-1234-123456789abc' 
and a2_0.active=true 
and a2_0.severity in ('STANDARD')

[beikov]

Alright, then you don't need unions and the thing becomes very simple.

CriteriaBuilder<Tuple> cb = cbf.create(em, Tuple.class).fromValues(Integer.class, "i", Collections.singleton(1));

for (var severity : searchParams.getSeverities()) {
    cb.selectSubquery(searchParams.getSeverity().toString())
	.from(AlertJpa.class, "r")
	.select("count(*)")
	.where("r.timestamp").between(searchParams.getStart()).and(searchParams.getEnd())
	.where("r.tenant").eq(searchParams.getTenant())
	.where("r.active").eq(searchParams.getActive())
	.where("r.severity").eq(searchParams.getSeverity());
}

var data = cb.getResultList();

[RaulPampliegaMayoral]

@beikov Thanks for your response... it looks so easy but i got stuck with this...

Again, thanks for your time!