How to create a dynamic subquery in QueryDsl without using Q-Classes?

Question

I'm kinda new to querydsl and currently working on a task where we want to access a OneToMany related table without using the generated Q-classes and perform a subquery on that. We're using QueryDsl 3.7.4. In this example case we've got a simple max-Statement on a date-column.

The statement I'm trying to achieve should look something like this:

select person
from person p
left join p.instructions as i
where i.validUntil = (
  select max(i.validUntil) from instruction i2 where i2.person = p
)

person entity:

@OneToMany(cascade = {..},mappedBy = "person")
private Set<Instruction> instructions = new HashSet<>();

instruction entity:

@Entity(name = "instruction")
public class Instruction {
  ...
  @Column(name = "validuntil", nullable = true)
  @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentLocalDate")
  private LocalDate validUntil;
  ...
}

variables:

query: select baseEntity from person baseEntity [..]
pathBuilder: baseEntity
entityPath: baseEntity.instructions
entityClazz: java.util.Set
propName: validUntil

So far, I tried different solutions:

if (entityClazz.isAssignableFrom(Set.class)) {

  CollectionPath<Object, PathBuilder<Object>> collection =
      pathBuilder.getCollection(entityName, entityClazz);

  query.leftJoin(collection.any());

  if ("date".equals(columnModel.getType())) {

    DateSubQuery<LocalDate> sub = new JPASubQuery().from(entityPath).where(collection.any().eq(pathBuilder))
        .unique(collection.any().getDate(propName, LocalDate.class).max());

    query.where(collection.any().getDate(propName, LocalDate.class).eq(sub));

  }
}

That results in an IllegalArgumentException with: Undeclared path 'baseEntity_instructions_64058'. Add this path as a source to the query to be able to reference it.
Which I kinda understand, since I didn't actually declare 'baseEntity_instructions_64058' anywhere. But how am I supposed to do it?

When I then try to replace the collection.any() statement with entityPath:

DateSubQuery<LocalDate> sub =
        new JPASubQuery().from(entityPath).where(entityPath.eq(pathBuilder))
            .unique(entityPath.getDate(propName, LocalDate.class).max());
query.where(entityPath.getDate(propName, LocalDate.class).eq(sub));

The generated query looks not bad to me:

select baseEntity
from Person baseEntity
  ...
left join baseEntity.instructions
where baseEntity.instructions.validUntil = (select max(baseEntity.instructions.validUntil)
from baseEntity.instructions
where baseEntity.instructions = ?1)

..but throws an org.hibernate.QueryException with the following message:

illegal attempt to dereference collection [person0_.auto_id.instructions] with element property reference [validUntil]

Another attempt, with an alias which looks like this:

PathBuilder<IFlattenable> alias = new PathBuilder<IFlattenable>(entityClazz,entityPath.as("subEntity").toString());

query.leftJoin(alias);

DateSubQuery<LocalDate> sub = new JPASubQuery().from(entityPath).where(alias.eq(entityPath))
    .unique(alias.getDate(propName, LocalDate.class).max());
query.where(alias.getDate(propName, LocalDate.class).eq(sub));

generated the following query:

select baseEntity
from person baseEntity
  ...
  left join Set baseEntity.instructions as subEntity
where baseEntity.instructions as subEntity.validUntil = (select max(baseEntity.instructions as subEntity.validUntil)
from baseEntity.instructions
where baseEntity.instructions as subEntity = baseEntity.instructions)

which ends up with antlr.NoViableAltException: unexpected token: Set. I don't see why it adds the "Set" to the query. Also I would expect that "baseEntity.instructions as subEntity" is only used once (at the actual join) and after that only the alias "subEntity" for reference.

I have been stuck on this for a while now, read a lot of stackoverflow and github issue posts and tried a lot combinations of various "solutions". Is anybody able to help me out with this?

Thanks in advance, I will buy you a drink :)

How to create a dynamic subquery in QueryDsl without using Q-Classes?

0 Answers0