Filtering in Spring Data JPA Repository using QueryDSL with a nullable foreign key

Question

I've been having this issue where I am unable to properly filter on a table using querydsl which has a nullable foreign key. I stripped down my use case into a very simple scenario.

Say we have 2 entities, MyEntity and TimeRangeEntity. My Entity only has an ID and a foreign key to the TimeRangeEntity. The TimeRangeEntity only has a start and an end time and an ID. BaseEntity, that these both extend from, only has the ID set with the @Id annotation.

@Entity
@Table(name = "TEST_OBJECT")
public class MyEntity extends BaseEntity {
    @OneToOne(cascade = { CascadeType.MERGE, CascadeType.PERSIST })
    private TimeRangeEntity actionTime;

    public TimeRangeEntity getActionTime() {
        return actionTime;
    }

    public void setActionTime(TimeRangeEntity actionTime) {
        this.actionTime = actionTime;
    }
}


@Entity
@DiscriminatorValue("static")
public class TimeRangeEntity extends BaseEntity {

    @Column(name = "START_TIME")
    private Instant startTime;

    @Column(name = "END_TIME")
    private Instant endTime;

    public Instant getStartTime() {
        return startTime;
    }

    public void setStartTime(Instant startTime) {
        this.startTime = startTime;
    }

    public Instant getEndTime() {
        return endTime;
    }

    public void setEndTime(Instant endTime) {
        this.endTime = endTime;
    }
}

I've constructed a default method in my repository to run a findAll with a predicate using querydsl to build the SQL syntax

@Repository
public interface MyRepository extends JpaRepository<MyEntity, Long>, QueryDslPredicateExecutor<MyEntity> {

    default Page<MyEntity> paginateFilter(PaginationInfo info, String filter){
        int page = info.getOffset() > 0 ? info.getOffset() / info.getLimit() : 0;
        PageRequest pageRequest = new PageRequest(page, info.getLimit(), new Sort(new Sort.Order(info.getSortDirection(), info.getSortProperty())));
        return findAll(createFilterPredicate(filter, myEntity), pageRequest);
    }

    default Predicate createFilterPredicate(String filter, QMyEntity root){
        BooleanBuilder filterBuilder = new BooleanBuilder();
        filterBuilder.or(root.id.stringValue().containsIgnoreCase(filter));
        filterBuilder.or(root.actionTime.startTime.isNotNull());
        return filterBuilder.getValue();
    }
}

I also wrote a test that should work given the code presented. What I'm trying to do is just filter based on ID. The caveat is that the FK to the TimeRange can be null. I'll note that this a contrived example to get my point across and the solution can't really be "just enforce the FK is not null."

@RunWith(SpringRunner.class)
@DataJpaTest(showSql = false)
@ContextConfiguration(classes = TestConfig.class)
@ActiveProfiles("test")
public class MyRepositoryTest {
    @Autowired
    private MyRepository sut;

    private static final int count = 3;

    @Before
    public void setup(){
        for (int i = 0; i < count; i++){
            sut.save(new MyEntity());
        }
    }

    @Test
    public void testPaginationWithStringFilter(){
        PaginationInfo info = new PaginationInfo();
        info.setOffset(0);
        info.setLimit(10);
        info.setSortDirection(Sort.Direction.ASC);
        info.setSortProperty("id");

        Page<MyEntity> page = sut.paginateFilter(info, "1");
        assertEquals(1, page.getTotalElements());

        page = sut.paginateFilter(info, "10");
        assertEquals(0, page.getTotalElements());
    }
}

The problem that I'm running into is that it isn't filtering on the ID if the FK is null. All I'm doing when I save is setting the ID. I know the problem is because I can see the filtering work properly when I comment out the line filterBuilder.or(root.actionTime.startTime.isNotNull()); but it doesn't work when I have that in.

This generates the following queries. The first is for the "working" filtering where I can filter based on ID (line commented out). The second is for the filtering with the actionTime included.

select myentity0_.id as id2_38_, myentity0_.action_time_id as action_t3_38_ from test_object myentity0_ where lower(cast(myentity0_.id as char)) like ? escape '!' order by myentity0_.id asc limit ?

select myentity0_.id as id2_38_, myentity0_.action_time_id as action_t3_38_ from test_object myentity0_ cross join time_range_entity timerangee1_ where myentity0_.action_time_id=timerangee1_.id and (lower(cast(myentity0_.id as char)) like ? escape '!' or timerangee1_.start_time is not null) order by myentity0_.id asc limit ?

Looking at this, I'm almost certain that this is due to the snipper cross join time_range_entity timerangee1_ where myentity0_.action_time_id=timerangee1_.id since it validates that the entities match, which they cannot if the range foreign key is null.

I've been pulling my hair out trying to get this conditional working that only checks the time range's table properties IF the FK is not null but I cannot find a way using querydsl. Any advice/guidance/code snippets would be stellar.

EDIT: Just translating to straight SQL, I got this query for the generated JPQL(translated to this example since I used it with real data):

select * from test_object cross join time_range range where test_object.action_time_id=range.id and lower(cast(test_object.id as char)) like '%1%';

With a null FK, that didn't return a row as expected. Changing this to a left join from a cross join ended up working properly.

select * from test_object left join time_range on test_object.action_time_id=time_range.id where lower(cast(test_object.id as char)) like '%1%';

With that, is there any way to specify a left join with the querydsl predicate executor? This seems like it'd be the solution to my problem!

Answer 1

Try to use Specification instead of Predicate

  private Specification<QMyEntity> createFilterPredicate(final String filter, final QMyEntity root) {
        return new Specification<QMyEntity>() {
            @Nullable
            @Override
            public Predicate toPredicate(Root<QMyEntity> root, CriteriaQuery<?> query,
                    CriteriaBuilder criteriaBuilder) {
                Join<Object, Object> actionTime = root.join("actionTime", JoinType.LEFT);
                return criteriaBuilder.or(criteriaBuilder.like(criteriaBuilder.lower(root.get("id")), "%" + filter + "%"), criteriaBuilder.isNotNull(actionTime.get("startTime")));
            }
        };
    }