Filter with Spring-Data and QueryDSL on nullable reference attribute

Question

I have following issue. I am using Jquery Datatable serverside and I am now implementing the search box. But I have an issue there in special case, when a dataset has an attribute, what is "null". So the dataset will not be found although it should found cos it matches on one attibute.

The situtation in beginning is like follows. You see there is a dataset with apprentice Fabio Bartels, who has not Fachrichtung. And a dataset with Viktoria.

enter image description here

Now when I search for Viktoria, the filter works as expected:

enter image description here

When I search for Fabio, then Dataset is not found:

enter image description here

=====

The problem I have is, that I don't know how to handle the filter, that a attribute will only be validated against the search string when the attribute is not null.

=====

Serverside Java Classes see like follows:

QueryClass:

class ContractSearchQuery {

private static QContract contract = QContract.contract;

static BooleanExpression getPredicate(final ContractSearch filter) {
    BooleanExpression predicate;

    if (filter == null || filter.isEmpty()) {
        // SHOW ALL PREDICATE ...
    } else {
        final String search = filter.getSearch();

        final List<BooleanExpression> expressions = new ArrayList<BooleanExpression>();

        // EXPRESSIONS CURRENTLY ONLY ON AUSZUBILDENDER AND FACHRICHTUNG
        // FOR SHOWCASE
        expressions.add(containsApprenticeName(search)); // AUSZUBILDENDER
        expressions.add(containsSpecialisation(search)); // FACHRICHTUNG

        BooleanExpression expression = expressions.get(INTEGER_ZERO);
        for (int i = 1; i < expressions.size(); i++) {
            expression = expression.or(expressions.get(i));
        }

        predicate = expression;
    }
    return predicate;
}

private static BooleanExpression containsApprenticeName(final String search) {
    final BooleanExpression expLastName = contract.apprentice.lastName.containsIgnoreCase(search);
    final BooleanExpression expFirstName = contract.apprentice.firstName.containsIgnoreCase(search);
    return expLastName.or(expFirstName);
}

private static BooleanExpression containsSpecialisation(final String search) {
    return contract.companyOccupationCombination.occupationCombination.specialisation.name.containsIgnoreCase(search);
}

}

Spring-Data-Repository Call:

final PageRequest pageRequest = new PageRequest(firstResult / maxResults, maxResults, orderSort);
final Page<Contract> page = contractRepository.findAll(predicate, pageRequest);
return page.getContent();

=======

Database:

By the way I recognized when I do direct request against my db with joining Specialisation Table, then I only get Fabio as record, when not joining Specialisation, I get all three persons. Maybe somethings to do with my issue:

select a.first_name, a.last_name from contract c 
    join company_occupation_combination coc on c.company_occupation_combination = coc.id 
    join occupation_combination oc on coc.occupation_combination = oc.id
    join apprentice a on c.apprentice = a.id

Result: "Fabio";"Bartels" "Viktoria";"Kruczek" "Lina";"Ehleiter"

With Join:

select a.first_name, a.last_name from contract c 
    join company_occupation_combination coc on         c.company_occupation_combination = coc.id 
    join occupation_combination oc on coc.occupation_combination = oc.id
    join specialisation s on oc.specialisation = s.id 
    join apprentice a on c.apprentice = a.id

Result: "Viktoria";"Kruczek"

====

EDIT: Okay, on db site I found out (with Hibernate and JPA I start forgetting SQL-Basices ;-)), that I need a left join for the nullable relation, so my query should result to an sql like:

select a.first_name, a.last_name from contract c 
join company_occupation_combination coc on c.company_occupation_combination = coc.id 
join occupation_combination oc on coc.occupation_combination = oc.id
left join specialisation s on oc.specialisation = s.id 
join apprentice a on c.apprentice = a.id

====

So my question is, how can I manage left Join when I have a Query-Class using QueryDSL and Spring-Data-Repository like mentioned above?

vtor vtor · Accepted Answer · 2015-02-19T21:46:39

If you really need left join, you can't achieve that via predicate (instead it is possible via sub-query)

To be able to do left-join, you will need JPAQuery. Assuming you have already configured repositories, and able to use EntitiManager, implement ContractRepositoryCustom , so that in your implementation you can have

@PersistenceContext(unitName = "unitname")
protected EntityManager entityManager;

public List<Contract> findAllContracts() {
    return new JPAQuery(entityManager, HQLTemplates.DEFAULT)
            .from(QContract.contract)
            .join(QContract.contract.companyOccupationCombination, QCompanyOccupationCombination.companyOccupationCombination)
            .join(QCompanyOccupationCombination.companyOccupationCombination.occupationCombination, QOccupationCombination.occupationCombination)
            .leftJoin(QOccupationCombination.occupationCombination.specialization, QSpecialization.specialization)
            .join(QSpecialization.specialization.apprentice, QApprentice.apprentice)
            .list(QContract.contract);
}

And for pagination you always apply limit(maxResults) and offset(firstResult)

Filter with Spring-Data and QueryDSL on nullable reference attribute

2 Answers