I am working on a large project where Blaze Persistence is at its core. Our architecture is complex, and all database operations ultimately rely on a specific Blaze Persistence query that looks like this:

return criteriaBuilder  
    .from(entityClass, "entityData")  
    .innerJoinOn(ChangeData.class, "changes")  
        .on("changes.id").eqExpression("entityData.id")  
    .end()  
    .select("entityData")  
    .select("ROW_NUMBER() OVER (PARTITION BY changes.id ORDER BY changes.sequence DESC)", "rn")  
    .select("changes.id", "changes_id")  
    .select("changes.sequence")  
    .selectNew(entityObjectBuilder);

Now, I need to wrap this entire query in a subquery to filter by rn = 1, similar to:

SELECT * FROM (the_above_cb_sql_output) WHERE rn = 1;
-- Effectively performing a GROUP BY changes.id using the highest changes.sequence

Using .fromSubquery – It requires manually binding all attributes of entityClass, which is not feasible because the class is provided as input (it’s meant to be generic). Using .fromEntitySubquery – I’m struggling to make this work due to a lack of documentation and examples. Attempted Solution: I tried using .fromEntitySubquery, but I couldn't get it to work:

return criteriaBuilder  
    .fromEntitySubquery(entityClass, "entityDataGroupedById")  
    .from(entityClass, "entityData")  
    .innerJoinOn(ChangeData.class, "changes")  
        .on("changes.id").eqExpression("entityData.id")  
    .end()  
    .where("rn").eq(1)  
    .end()  
    .select("entityData")  
    .select("ROW_NUMBER() OVER (PARTITION BY changes.id ORDER BY changes.sequence DESC)", "rn")  
    .select("changes.id", "changes_id")  
    .select("changes.sequence")  
    .selectNew(entityObjectBuilder);

Question: How can I wrap my existing Blaze Persistence query in a subquery to filter by ROW_NUMBER() = 1, while keeping it generic (without manually binding all attributes of entityClass)?

Any guidance or examples would be greatly appreciated!

I am working on a large project where Blaze Persistence is at its core. Our architecture is complex, and all database operations ultimately rely on a specific Blaze Persistence query that looks like this:

return criteriaBuilder  
    .from(entityClass, "entityData")  
    .innerJoinOn(ChangeData.class, "changes")  
        .on("changes.id").eqExpression("entityData.id")  
    .end()  
    .select("entityData")  
    .select("ROW_NUMBER() OVER (PARTITION BY changes.id ORDER BY changes.sequence DESC)", "rn")  
    .select("changes.id", "changes_id")  
    .select("changes.sequence")  
    .selectNew(entityObjectBuilder);

Now, I need to wrap this entire query in a subquery to filter by rn = 1, similar to:

SELECT * FROM (the_above_cb_sql_output) WHERE rn = 1;
-- Effectively performing a GROUP BY changes.id using the highest changes.sequence

Using .fromSubquery – It requires manually binding all attributes of entityClass, which is not feasible because the class is provided as input (it’s meant to be generic). Using .fromEntitySubquery – I’m struggling to make this work due to a lack of documentation and examples. Attempted Solution: I tried using .fromEntitySubquery, but I couldn't get it to work:

return criteriaBuilder  
    .fromEntitySubquery(entityClass, "entityDataGroupedById")  
    .from(entityClass, "entityData")  
    .innerJoinOn(ChangeData.class, "changes")  
        .on("changes.id").eqExpression("entityData.id")  
    .end()  
    .where("rn").eq(1)  
    .end()  
    .select("entityData")  
    .select("ROW_NUMBER() OVER (PARTITION BY changes.id ORDER BY changes.sequence DESC)", "rn")  
    .select("changes.id", "changes_id")  
    .select("changes.sequence")  
    .selectNew(entityObjectBuilder);

Question: How can I wrap my existing Blaze Persistence query in a subquery to filter by ROW_NUMBER() = 1, while keeping it generic (without manually binding all attributes of entityClass)?

Any guidance or examples would be greatly appreciated!

• java
• hibernate
• hibernate-criteria
• blaze-persistence
• criteriabuilder

1 Answer 1

Looks like a lateral join might be necessary in this case i.e.

return criteriaBuilder  
    .from(entityClass, "entityData")  
    .innerJoinLateralOnEntitySubquery("entityData", ChangeData.class, "changes", "c")
        .orderByDesc("c.sequence")
        .setMaxResults(1)
    .end()
        .on("changes.id").eqExpression("entityData.id")  
    .end()  
    .select("entityData")
    .select("changes.id", "changes_id")  
    .select("changes.sequence")  
    .selectNew(entityObjectBuilder);