Collection Query Issues with QueryDSL JPA and PostgreSQL

Question

I'm currently using QueryDSL 3.7.2 with Spring-Data-JPA. I'm building a predicate, then passing it to a repository that extends QueryDslPredicateExecutor and queries a PostgreSQL 9.3 database.

I had everything working correctly, but have run into two scenarios that are causing me issues. I'm not sure if I'm approaching this correctly, and I can't seem to find any examples that quite match the scenarios.

Scenario 1:

For this, I have an entity with a list of children. I wanted an "AND" on two of the child properties, so I used a JPASubQuery:

final QChild any = QParent.parent.children.any();
final JPASubQuery subQuery = new JPASubQuery();
final QChild qChild = QChild.child;
subQuery.from(qChild)
        .where(qChild.code.eq(codeValue)
            .and(qChild.date.isNull()));
predicateBuilder.and(any.in(subQuery.list(qChild)));

So basically I want to fetch any Parent objects where the Child has a code of codeValue and a null date. This worked perfectly when the Child had a surrogate key in the database (an ID column). This generated the following query when passed to the repository:

select 
    count(parent0_.parent_id) as col_0_0_ 
from parent_tab parent0_ 
where exists (
    select 1 
    from child_tab child1_ 
    where parent0_.parent_id=child1_.parent_id 
    and (
        child1_.child_id 
        in (
            select child2_.child_id 
            from child_tab child2_ 
            where child2_.status=? and (child2_.date is null)
        )
    )
)

The problem arises when we change the surrogate key to a natural key with serveral fields (code, status, parent_id and name). The following query is then generated:

select 
    count(parent0_.parent_id) as col_0_0_ 
from parent_tab parent0_ 
where exists (
    select 1 
    from child_tab child1_ 
    where parent0_.parent_id=child1_.parent_id 
    and (
        (child1_.code, child1_.status, child1_.parent_id, child1_.name) 
        in (
            select (child2_.code, child2_.status, child2_.parent_id, child2_.name) 
            from child_tab child2_ 
            where child2_.status=? and (child2_.date is null)
        )
    )
)

This isn't valid, and throws the following exception:

ERROR: subquery has too few columns

From what I can gather, any().in(subQuery.list(qChild)) is the part that's causing the problem. From all the examples I've found, this is what's being done - but it's also with a surrogate key. Is there something that I'm missing here?

Scenario 2:

The second scenario is dealing with a PostgreSQL only feature - the pg_trgm extension. This extension is used for fuzzy searching, and allows us to use two specific commands - "similarity(x, y)" and "x % y". The former will return a real number representing how close a match the parameters are, and the second will return a boolean if the values are above 0.3 (by default). Originally I was using the former, like so:

final NumberExpression<Double> nameExpression = NumberTemplate.create(Double.class, "similarity({0}, {1})", QParent.parent.name ConstantImpl.create(name));
predicateBuilder.or(nameExpression.goe(0.3));

This worked perfectly, but unfortunately "similarity(x, y)" doesn't use trigram indexes, so I wanted to change to the "%" operator, which does. I thought it should be as easy as the following:

final BooleanExpression nameExpression = BooleanTemplate.create("{0} % {1}", QParent.parent.name, ConstantImpl.create(name));
predicateBuilder.or(nameExpression.isTrue());

Unfortunately this doesn't work, and throws the following exception:

java.lang.IllegalArgumentException: Parameter value [true] did not match expected type [java.lang.String (n/a)]
    at com.mysema.query.jpa.impl.JPAUtil.setConstants(JPAUtil.java:55) [querydsl-jpa-3.7.2.jar:]
    at com.mysema.query.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:130) [querydsl-jpa-3.7.2.jar:]
    at com.mysema.query.jpa.impl.AbstractJPAQuery.count(AbstractJPAQuery.java:81) [querydsl-jpa-3.7.2.jar:]
    at org.springframework.data.jpa.repository.support.QueryDslJpaRepository.findAll(QueryDslJpaRepository.java:141) [spring-data-jpa-1.9.2.RELEASE.jar:]

The problem seems to be that it's expecting a String instead of a Boolean for the JavaType in the AbstractJPAQuery. Excluding isTrue() results in the following exception:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: % near line 3, column 19

The query is the following:

select count(parent)
from com.test.Parent parent
where parent.name % ?1

I solved this by using a custom dialect, and registering the following function:

registerFunction("sim", new SQLFunctionTemplate(StandardBasicTypes.BOOLEAN, "?1 % ?2"));

Then I can use the following:

final BooleanExpression nameExpression = BooleanTemplate.create("sim({0}, {1})", QParent.parent.name, ConstantImpl.create(name));
predicateBuilder.or(nameExpression.isTrue());

When expanding on this, I wanted to also check the name of the child entities. This led to the following:

final BooleanExpression childExpression  = BooleanTemplate.create("sim({0}, {1})", QParent.parent.children.any().name, ConstantImpl.create(name));
predicateBuilder.or(childExpression.isTrue());

This, however, doesn't work, and throws the following exception:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: exists near line 3, column 7

The query is:

select count(parent)
from com.test.Parent parent
where exists (select 1
from parent.children as parent_children_4652c
where sim(parent_children_4652c.name, ?1)) = ?2

The exception seems to point to the "exists", but I'm not sure why. As a result, I tried to create a sub-query (like scenario 1):

final BooleanExpression childExpression  = BooleanTemplate.create("sim({0}, {1})", QChild.child.name, ConstantImpl.create(name));
final QChild child = QChild.child;
final JPASubQuery subQuery = new JPASubQuery();
subQuery.from(child)
    .where(childExpression.isTrue());
predicateBuilder.or(QParent.parent.children.any().in(subQuery.list(child)));

This, however, runs into the same problem as scenario 1, where the child entity has a composite key, and the any().in() doesn't seem correct.

So there's a few questions here:

1. Is there a way to use the % operator without registering a custom function in the dialect?
2. Am I querying the children correctly with the sim() function?
3. What's the correct way to craft a sub-query with a composite key?

Any additional pointers or help would be greatly appreciated too.

Answer 1

Figured out how to do both (though I haven't answered all questions from Scenario 2). My problem was thinking that I wanted the entity returned. A good night's sleep made me see it clearly.

Instead of returning qChild from the subquery list, I should have been returning its parent ID. Then I simply needed to check if the parent ID was in that list:

final String code = "code";
final String name = "name";
final JPASubQuery subQuery = new JPASubQuery();
final QParent parent = QParent.parent;
final QChild child = QChild.child;
subQuery.from(child)
        .where(child.id.code.eq(code)
                .and(child.id.name.eq(name)));
predicateBuilder.or(parent.id.in(subQuery.list(child.id.parentId)));

For the second scenario, I kept the custom Dialect with the registerFunction for my trigram operator, and used the following subquery:

final String name = "name";
final QParent parent = QParent.parent;
final QChild child = QChild.child;
final BooleanExpression newExpression  = BooleanTemplate.create("sim({0}, {1})",
        child.id.name, ConstantImpl.create(name));
final JPASubQuery subQuery = new JPASubQuery();
subQuery.from(child)
        .where(newExpression.isTrue());
predicateBuilder.or(parent.id.in(subQuery.list(child.id.parentId)));

Everything is working correctly, though I still wonder if there's a way to simply use the trigram operator without registering a custom function.